Re: Suggestions solicited - Change Column Datatype from Number to

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 <DWILLIAMS_at_LIFETOUCH.COM> 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.
> > dwilliams_at_lifetouch.com
> >
> > -----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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).