ORA-00997: Illegal use of datatype

One day you've decided to do a simple backup for your table:

create tab1_bkp as select * from tab1;

And ops - you are getting this:

ORA-00997: Illegal use of datatype.

The reason for it is that one or more columns in your table have LONG data type which could only be used for select.

The same error you will get when you try to move your table to a different tablespace.

There are a couple of ways to get around that. One is to do the export and import. This will work if you need to bring the data from this table into a different database (because you would not be able to use a database link or a COPY command due to the same reason).

The other one is to create your backup table by converting this LONG column to CLOB:

create table tab1_bkp as select a, b, c,...,to_lob(k), l,m from tab1;