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 <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
For many people, Oracle performance 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.
Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.7 Information in this document applies to any platform. Goal#1. How to solve the following situation?
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