move table with datatype long

jeyagowri · #1 (**permalink**) 04-24-03, 07:40

ALTER TABLE APPFLAG MOVE TABLESPACE PAY_DATA
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

I have one table which has a LONG datatype, i have to move this table to another tablespace.
the sourcedb was using system tablespace but in targetdb we need to use pay_data tablespace.
i have managed to move all the other tables however i have the above error with this one table.
how can i solve this.
there are data in this table therefore i will not be able to create it again.
please advise.
thanks.

OBRP · #2 (**permalink**) 04-24-03, 08:08

Consider import/export utilities or COPY command from SQLPLUS.

HTH,
OBRP

osy45 · #3 (**permalink**) 04-24-03, 10:33

the following works for me:

exp the table tbl_with_long

imp usr/pwd@sid file=tbl_with_long show=y log=crttbl.sql

edit the crttbl.sql and adjust the storage clause to the new tablespace

drop all the constraints ... in the system tablespace for the table tbl_with_long

imp sys/pwd@sid file=tbl_with_long fromuser=fromusr touser=touser ignore=yes rows=y