Hi Gurus,
SQL> select * from v$version
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
I need to increase length of a CHAR datatype column in a table (currently it is CHAR(3) and want to change it to CHAR(4) ). Following are more details about table:-
- Contains more than 20 million rows.
- Table contains multiple indexes.
- Table is referenced by multiple tables(around 60 tables) and it references multiple tables(around 3)
- Table is having 4 complex triggers. Triggers are calling procedures and packages.
What I tried so far
1. Disabled all the triggers and ran ALTER statement to increase the length. It took hours and did not complete in reasonable time frame, though no error, but it took more than 12 hours.
2. Tried DBMS_REDEFINITION package but it did not work with Oracle 10g standard version.
What I am thinking to try next
I am thinking following approach:
Say, my existing table name is OLD_T1 where column length need to increase
-Create a copy table (with increased column length) of OLD_T1, Say NEW_T1
-Move data from in OLD_T1 to new table NEW_T1
- Drop OLD_T1 (or rename to OLD_T1_TMP)
-Rename NEW_T1 to OLD_T1
-Create all the indexes, triggers, constraints etc.
I want to confirm, if my approach is correct ? Or Some one has better idea of doing this ?
Please note: I know CHAR datatype gives problem but this is an old system and I don't have authority to change the database design.
Appreciate any comment/suggestion
Thanks in advance