Oracle Illegal use of long datatype when moving tablespace to bigfile I am currently migrating all the tables in my database to a bigfile tablespace. I have written a script to do this and all tables are migrated successfully except one. I am given the error:

Error report:
SQL Error: ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"

And I am converting the tablespaces by:

alter table mytable move tablespace bigfile_tablespace;


Changing the column type isn't an option.

I have a script that creates all the tablespace from scratch using bigfile and it goes through fine with this table so I am little stuck on this...
To move the table without changing the column datatype I believe you'll need to export the table then import it into the new tablespace. 

Note that if you're using Oracle 10g or later use expdp and impdp instead of exp/imp. (See: http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm)

You should use a command like:


  exp file=mytable.dmo log=mytable .log tables=mytable  feedback=1


To export the table. Then drop it in the old tablespace and create it in the new tablespace.

Then use:


  imp file=mytable.dmo log=mytable.log full=y feedback=1 ignore=y


To import the table. Hope this helps.

References:


http://www.orafaq.com/wiki/Import_Export_FAQ
http://psoug.org/reference/export.html
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm