Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Alter length of a Table column (CHAR datatype)

User_08DMAFeb 12 2014 — edited Feb 13 2014

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

This post has been answered by sb92075 on Feb 13 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 13 2014
Added on Feb 12 2014
6 comments
3,563 views