Steps:

1. Add New temp Column with desired datatype.

SQL> Alter Table <table_name>
Add temp datatype(size);

2. Update the data into new column.

SQL> Update <table_name>
set temp = <original_columnname>
where <original_columnname> is not null;

3. Drop the original Column.

SQL> Alter table <table_name>
Drop column <original_columnname>;

4. Rename the new column to original Name.

SQL> Alter table <table_name>
rename column temp to <original_columnname>;

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

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

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

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

Below is the code to close the reporting engine, if required by the application.

———————————————-
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.

What are the required steps for getting certified?

1. Select a track
2. Prepare for the test
3. Schedule the test

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

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

For more details and any questions, you can reach me at shoaib@aezones.com