Rachel Carmichael wrote: > > Dennis, > > That's a good thought, and it works if you don't have grants, > constraints or dependencies on the original table. > > If you drop table1, you lose them all > > Rachel > > --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > > Deepak, If there are many columns on these tables, your method may be > > best. > > However, this will generate a lot of redo. You can usually accomplish > > this > > with a CTAS nologging, which won't generate redo. If you really don't > > want > > to change the location, you can: > > create table temp as select * from table1 nologging > > drop table table1 > > create table table1 (column, column . . . ) as select * from temp > > nologging > > drop table temp > > > > > > > > > > Dennis Williams > > DBA, 40%OCP > > Lifetouch, Inc. > > [EMAIL PROTECTED] > > > > -----Original Message----- > > Sent: Thursday, October 24, 2002 3:15 PM > > To: Multiple recipients of list ORACLE-L > > > > > > We have a need to change the datatype of several columns in a table > > from > > number to varchar2. Most of the rows have data in these columns hence > > a > > direct 'alter table ...' will not work. > > > > We plan to create a temp table, move the data from these colums to > > that > > table, modify the column datatype from number to varchar2 and then > > update > > the colums with the data that was moved to the temp table. > > > > Any suggestions/comments or a better way to do this ? Oh, and we are > > on > > 8.1.7.1 > > > > thanx > > deepak > >
Hmmm, generating the suitable script from USER_TAB_PRIVS, USER_CONSTRAINTS, USER_IND_COLUMNS and USER_CONS_COLUMNS is not too difficult ... at least if you are familiar with the dictionary. But why the second CTAS ? What about RENAME ? Seems faster to me ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
