http://www.orafaq.com/forum/t/21725/0/
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:588223421081
Move doesnt support Long datatypes.
You can either convert them to LOBs and then move or do exp/imp of the table with the LONG column
or create the table with LONG in the locally managed tablespace and copy the data from the old table using PL/SQL loop
or CTAS with to_lob in the locally managed tablespace..
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y LONG
SQL> alter table t move;
alter table t move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
-- You can create the new table in the Locally Managed tablespace
SQL> create table t_lob tablespace users as select x,to_lob(y) y from t;
Table created.
SQL> desc t_lob
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y CLOB
-- Now you can drop the old table and rename the new table
-- Or you can move the LOB table to the locally managed tablespace
SQL> alter table t_lob move;
Table altered.
-- Or you can precreate the new table with LONG in the locally managed tablespace and do exp/imp
-- export the Long table
SQL> !exp / file=t.dmp tables=t compress=n
Export: Release 9.2.0.3.0 - Production on Tue Mar 2 09:32:30 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 2 rows exported
Export terminated successfully without warnings.
-- just rename the old table for reference purposes
SQL> rename t to tbak;
Table renamed.
-- Create the LONG table in the locally managed tablespace
SQL> create table t(x int,y long) tablespace users;
Table created.
-- now import the data
SQL> !imp / file=t.dmp tables=t ignore=y
Import: Release 9.2.0.3.0 - Production on Tue Mar 2 09:33:43 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$ORACLE's objects into OPS$ORACLE
. . importing table "T" 2 rows imported
Import terminated successfully without warnings.
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y LONG