Oracle-L: RE: Suggestions solicited - Change Column Datatype from Number to

From: <Rick_Cale_at_teamhealth.com>
Date: Fri, 25 Oct 2002 04:58:29 -0800
Message-ID: <F001.004F3874.20021025045829@fatcity.com>

The user stated they were using 8.1.7.1 :)

                                                                                                      
                    Rachel                                                                            
                    Carmichael           To:     Multiple recipients of list ORACLE-L                 
                    <wisernet100_at_y        <ORACLE-L_at_fatcity.com>                                      
                    ahoo.com>            cc:                                                          
                    Sent by:             Subject:     RE: Suggestions solicited - Change Column       
                    root_at_fatcity.c        Datatype from Number to                                     
                    om                                                                                
                                                                                                      
                                                                                                      
                    10/24/2002                                                                        
                    08:38 PM                                                                          
                    Please respond                                                                    
                    to ORACLE-L

9i -- dbms_redefinition

should do it for you :)

DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> wrote:
> Yeah, well the developers are always pi$$ed about something anyway.
> ;-)
>
> Seriously, good point, I've been spoiled, because most of my
> applications
> have kept the RI in the application as was being discussed earlier.
> Now they
> are switching to Java and in future projects will be implementing RI
> in
> Oracle, so I'll have to be more careful about that.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Thursday, October 24, 2002 4:18 PM
> To: Multiple recipients of list ORACLE-L
> to
>
>
> 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
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.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).
>
>
> __________________________________________________
> Do you Yahoo!?
> Y! Web Hosting - Let the expert host your web site
> http://webhosting.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).

Do you Yahoo!?
Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rick_Cale_at_teamhealth.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).

Received on Fri Oct 25 2002 - 07:58:29 CDT