Re: Converting an existing table column from char to varchar2
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 29 Feb 2012 14:22:58 -0800 (PST)
Message-ID: <1330554178.73156.YahooMailNeo_at_web160905.mail.bf1.yahoo.com>
Issues I can see encountring:
ORA-01555 on the column update if you're using manual UNDO or the UNDO tablespace is small. Column update takes a long tme if the indexes aren't dropped or set to unusable. Row migration can occur if the varchar2 size is much greater than the original char definition as the row could exceed the available update space in the block. David Fitzjarrell
From: fmhabash <fmhabash_at_gmail.com>
To: ORACLE-L <oracle-l_at_freelists.org> Sent: Wednesday, February 29, 2012 12:08 PM Subject: Converting an existing table column from char to varchar2
Date: Wed, 29 Feb 2012 14:22:58 -0800 (PST)
Message-ID: <1330554178.73156.YahooMailNeo_at_web160905.mail.bf1.yahoo.com>
Issues I can see encountring:
ORA-01555 on the column update if you're using manual UNDO or the UNDO tablespace is small. Column update takes a long tme if the indexes aren't dropped or set to unusable. Row migration can occur if the varchar2 size is much greater than the original char definition as the row could exceed the available update space in the block. David Fitzjarrell
From: fmhabash <fmhabash_at_gmail.com>
To: ORACLE-L <oracle-l_at_freelists.org> Sent: Wednesday, February 29, 2012 12:08 PM Subject: Converting an existing table column from char to varchar2
I have a need to do this conversion in a large table (10M) rows on a 10.2.0.4.0 database. My research let me to the following facts ...
Option 1) Using 'alter table .."
- Set BLANK_TRIMMING = TRUE
- disable triggers, constraints, drop indexes (?)
- varchar2 size must be >= current char size
- update varchar2 column set rtrim
Option 2) Rebuild table
If you have done this in the past, how was your procedure different and what issue have encountered if any.
Thank you.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 29 2012 - 16:22:58 CST