ALTER TABLE MODIFY COLUMN issues
btoorgDec 1 2010 — edited Dec 1 2010I have a 10g Standard Edition database recently upgraded from Oracle 8i. All VARCHAR2 column definitions have now become VARCHAR2 byte definitions eg VARCHAr2(10 char) has become VARCHAR2(10 bytes). I need to change all defintions back to VARCHAr2 chars.
I have done this successfully for small tables using ALTER TABLE tablename modify column (varchar2(10 char)).
However I have a large table (2.3G) with many columns that need altered. The first issue that I ran into was the archive logs filled up a disk partition. As this is test I was able to switch into NOARCHIVE log mode.
The next issue was that the UNDO tablespace filled up (11G). I can't add anymore space to this.
It also takes hours.
I am now planning to create a new table with the correct column defintions and use 'INSERT into new_table select * from old_table'. I may have to break it up into several runs using a particular set of values for one variable.
Does anyone have any better ideas how I should handle this issue. Is ther a better way?
Thoughts and ideas gratefully received
Best wishes
Karen