Oracle 10g/11g OCA Preparation Guidelines

The road map which help the people who are looking for Oracle 1og and 11g Certification exam, where to start.
What are the required steps for getting Oracle certified?
1. Select a track
2. Prepare for the test
3. Schedule the test
Step 1. Select the track
Oracle Database Administrator:

• Oracle 11g (OCA, OCP, OCM)
• Oracle 10g DBA (OCA, OCP, OCM)
• Oracle 9i DBA (OCA, OCP, OCM)

Oracle 9i or 10g Forms Developer:

• Oracle PL/SQL Developer Certified Associate
• Oracle Forms Developer Certified Professional

For the complete list, follow the Oracle university website and check where you fit in. http://www.oracle.com/education/certification/index.html?starthere.html
For Oracle 11g OCA, only two exams are required:
1. 1Z0-051 Oracle Database 11g: SQL Fundamentals I
or 1Z0-047 Oracle Database SQL Expert
2. 1Z0-052  Oracle Database 11g: Administration I

Step 2. Prepare for the test.
Recommended Ref. Oracle Books

• Oracle Database 10g OCP Certification All-In-One Exam Guide
• Sybex.Inc.OCA.Oracle.10g.Administration.I
• Oracle.Database.10g.Administration.Workshop.I (from Oracle University)

Read the Oracle Documentation Guides

• 10g Concepts
• 10g DB Admin Guide

(Download from Oracle full documentation http://tahiti.oracle.com)
Practice test

• Self-Test Software (250-300 questions)

http://www.selftestsoftware.com.
3. Schedule the test.

• Check your nearest Sylvan Prometric testing center

http://www.prometric.com

Change the Table Column Datatype with data using SQL

Follow the simple steps to change the column datatype which is also having a data inside.
Normally, it’s not possible to change the column datatype with the existing data.
Steps:
1. Add New temp Column with desired datatype.
SQL> Alter Table
Add temp datatype(size);

2. Update the data into new column.
SQL> Update
set temp =
where is not null;

3. Drop the original Column.
SQL> Alter table
Drop column ;

4. Rename the new column to original Name.
SQL> Alter table
rename column temp to ;


Note:- change the table_name, datatype, Original_columnname and size

Oracle Database Performance Tuning

For many people, Oracle database performance tuning is a hard thing and difficult to achieve. Actual user will not see any change done by the experts after the application deployment, So, I will start writing some articles on the Oracle performance tuning, required by any Application built on Oracle Database.

Most of issues of performance tuning will auto resolved, if the database design is properly done.
We will start with the database design improvements.

Close Oracle Reporting Engine Procedure

In the Application development, during running the oracle report engine every time you run the report.
Below is the code to close the reporting engine, if required by the application.



You can make this procedure using Oracle Forms Builder 6i

———————————————-
PROCEDURE close_rbe IS

  v_win_handle NUMBER;

  timer_id TIMER;

 

BEGIN

  v_win_handle := win_api_session.findAppwindow(’Reports Background Engine’, ‘rwrbe60.exe’, ‘WINDOW’, WIN_API.WINCLASS_REPORTSSERVER_V6, FALSE);

–  MESSAGE(TO_CHAR(v_win_handle), acknowledge);

  IF v_win_handle > 0 THEN

    IF win_api_session.Find3rdPartyApp (’Reports Background Engine’, ‘rwrbe60.exe’, ‘WINDOW’, TRUE, WIN_API.WINCLASS_REPORTSSERVER_V6, FALSE) THEN

      win_api_shell.sendkeys(v_win_handle, ‘%{F4}’, true);

    END IF;

  END IF;

  timer_id := Find_Timer(’Delay’);

  IF NOT Id_Null(timer_id) THEN

            Delete_Timer(timer_id);

  END IF;

  timer_id := CREATE_TIMER(’Delay’, 100, NO_REPEAT);

END;
——————————————————-

Step2: call  this procedure in the application to close oracle background reporting engine.
make sure, your application has such requirements.

DBMS_METADATA Error

Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.7 Information in this document applies to any platform. 

SQL> select dbms_metadata.get_ddl(’TABLE’,’mytable‘,’myuser‘) from dual;

ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'n'
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3688
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4544
ORA-06512: at "SYS.DBMS_METADATA", line 466
ORA-06512: at "SYS.DBMS_METADATA", line 629
ORA-06512: at "SYS.DBMS_METADATA", line 1246
ORA-06512: at line 1

Prior to 9.2.0.6, this situation is caused by:Bug.3361288 (80) AFTER
REGISTERING XML SCHEMA IN AL32UTF8 DATABASE, EXPORT FAILS WITH ORA-24324 

Solution # 1:

The recommendations are documented in:

Note.279065.1 Ext/Mod Full Export of Database fails with EXP-00056 ORA-06502 ORA-31605 ORA-22921

Solution#1. The solution is to reload the XML API:
 

Step 1. SQL> alter system enable restricted session;

No Body can login or start a new session while running the scripts

Step 2. run: (from $ORACLE_HOME/rdbms/admin):
 

catnomet.sql
rmxml.sql
to remove the xml subsystem

After that recreate it by running following scripts:
catxml
utlcxml.sql
prvtcxml.plb
catmet.sql 

 All the scripts can be run only on testing environment and run in production at your own risk.