How to copy a table having long column

[khobar]

Hi

I have a table created with long datatype

CREATE TABLE T1(ID NUMBER, text_data long);

How do i copy/replicate to another table. If i use

create table t2 as select * from t1 it doesn't work.

In oracle 8i i used copy command as shown below

copy from uid/pwd@inst to uid/pwd@inst CREATE T2 USING SELECT * FROM T1;

But the same command if execute in oracle 9i it comes with invalid identifier error. Is copy command desupported in 9i ?

What is the equivalent command should i use

regards
khobar

 

[Dagon]

I have used this command successfully in 9.2.0.4. I'd check that you can see T1 from the user who is running the command.

 

[SantaMufasa]

Khobar,

Another alternative worth considering is to modify your LONG column to CLOB:

ALTER TABLE t1 MODIFY text_data CLOB;

table altered.

...then, you can treat "text_data" in the same manner that you would any VARCHAR2, including:

CREATE TABLE T2 USING SELECT * FROM T1;

...at the SQL*Plus prompt.

Let us know what you end up doing.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:00 (05Oct04) UTC (aka "GMT" and "Zulu"), 11:00 (05Oct04) Mountain Time)

 

[khobar]

Thanks for everybody who replied.

I was using oracle 8i client sqlplus utility to execute the copy command on the oracle 9i database and there were some bugs reported on metalink. When i used oracle 9i client to login into sqlplus and executed copy command it worked


regards
khobar