[Oracle-l] RE: Suggestions solicited - Change Column Datatype from Number to

Rachel Carmichael

at Oct 24, 2002 at 9:18 pm

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 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).

reply | permalink

Rick_Cale_at_teamhealth.com

at Oct 24, 2002 at 9:18 pm

⇧

Could you preserve the constraints and indices if you do somthing similar
like

create table temp as select * from table1 nologging
TRUNCATE table table1

ALTER TABLE table1
NOLOGGING;

ALTER TABLE table1 MODIFY(column...);

Do a direct load insert

ALTER TABLE table1 LOGGING;

DROP TABLE temp

I have not done direct load insert but read it can be done for NOLOGGING
Rick

DENNIS WILLIAMS

Sent by: cc:
root_at_fatcity.co Subject: RE: Suggestions solicited - Change Column
m Datatype from Number to

10/24/2002
04:35 PM
Please respond
to ORACLE-L

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).

--
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).

reply | permalink

Stephane Faroult

at Oct 24, 2002 at 9:54 pm

⇧

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 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).

reply | permalink

DENNIS WILLIAMS

at Oct 24, 2002 at 10:04 pm

⇧

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 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).

reply | permalink

DENNIS WILLIAMS

at Oct 24, 2002 at 10:14 pm

⇧

Stephane - You asked:

But why the second CTAS ? What about RENAME ? Seems faster to me ...

Well, it depends on whether you are okay with changing locations of the
table. Personally, I'm with you, just accomplish everything with one move of
the data. But I figured someone would object, insisting on ending up with
everything in the original location.

Dennis Williams
DBA, 40%OCP

Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, October 24, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
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 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).
--
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).

reply | permalink

Rachel Carmichael

at Oct 25, 2002 at 12:33 am

⇧

Stephane,

It's easy if there is only one level of dependency... but what if (as
they did in one of my systems) there are levels of dependency? It's
sort of like trying to write (or rewrite) the ideptree view. Only you
have to save off the scripts, in the proper order. Oh yes, don't forget
views and stored programs which can become invalid.

It's NOT trivial.

Rachel

Stephane Faroult wrote:

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 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).

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).

reply | permalink

Rachel Carmichael

at Oct 25, 2002 at 12:38 am

⇧

9i -- dbms_redefinition

should do it for you :)

DENNIS WILLIAMS 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 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).

reply | permalink

Rick_Cale_at_teamhealth.com

at Oct 25, 2002 at 12:58 pm

⇧

The user stated they were using 8.1.7.1 :)

Rachel
Carmichael To: Multiple recipients of list ORACLE-L

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 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 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).

reply | permalink

Rachel Carmichael

at Oct 25, 2002 at 2:28 pm

⇧

yes I know, which is why I did not suggest it to the original poster.

Dennis was talking about *his* future projects, which is why I
suggested it for him

Rick_Cale_at_teamhealth.com wrote:

The user stated they were using 8.1.7.1 :)

Rachel

Carmichael To: Multiple recipients
of list ORACLE-L

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 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 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!?

=== message truncated ===

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).

reply | permalink

Rick_Cale_at_teamhealth.com

at Oct 25, 2002 at 4:19 pm

⇧

Ooops!!! I missed where the thread changed from 8i to 9i

Rachel
Carmichael To: Multiple recipients of list ORACLE-L

ahoo.com> cc:
Sent by: Subject: RE: Suggestions solicited - Change Column
root_at_fatcity.c Datatype from Number to
om

10/25/2002
10:28 AM
Please respond
to ORACLE-L

yes I know, which is why I did not suggest it to the original poster.

Dennis was talking about *his* future projects, which is why I
suggested it for him

Rick_Cale_at_teamhealth.com wrote:

The user stated they were using 8.1.7.1 :)

Rachel

Carmichael To: Multiple recipients
of list ORACLE-L

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 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 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!?

=== message truncated ===

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).

reply | permalink

DENNIS WILLIAMS

at Oct 25, 2002 at 6:09 pm

⇧

Rachel, Rick - And I really appreciated Rachel's suggestion because the new
project will be on 9i, so it makes sense for me to change my methods while
taking advantage of the latest Oracle features. Thanks to the goddess!

Dennis Williams
DBA, 40%OCP

Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, October 25, 2002 9:29 AM
To: Multiple recipients of list ORACLE-L
to

yes I know, which is why I did not suggest it to the original poster.

Dennis was talking about *his* future projects, which is why I
suggested it for him

Rick_Cale_at_teamhealth.com wrote:

The user stated they were using 8.1.7.1 :)

Rachel

Carmichael To: Multiple recipients
of list ORACLE-L

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 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 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!?

=== message truncated ===

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).

reply | permalink

group	oracle-l @
categories	oracle
posted	Oct 24, '02 at 8:35p
active	Oct 25, '02 at 6:09p
posts	12
users	4
website	oracle.com

responses	expanded	Hotkey: s
font	variable	Hotkey: f
user style	avatars	Hotkey: a

[Oracle-l] RE: Suggestions solicited - Change Column Datatype from Number to

Search Discussions

Related Discussions

4 users in discussion