Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Mar 8 '13 at 13:06

This question came from our site for professional and enthusiast programmers.

up vote 3 down vote accepted

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:

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.


share|improve this answer
exp is deprecated so use expdp – Fraser Mar 8 '13 at 11:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.