ORA-00997: illegal use of LONG datatype

Error :

I got this error " ORA-00997: illegal use of LONG datatype" while creating the backup of table with long datatype .

SQL> create table test as select * from csn_user;
create table test as select * from csn_user
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Solution :

Solution 1 :

1.export the table
2.rename the table
3.import the table

But above solution is not possible , if tables is highly accessible . In that case , you can go for solution2

Solution 2 :

Table thats backup need to be created is CSN _USER ( owner : CSN ) .


Step1 : Take the export of the table

exp file=csn_user_2409.dmp log=csn_user_2409.log tables=csn.csn_user

Username: / as sysdba

Step 2 : Now import the table the table in a schema other that CSN . In that schema there shouldn't be any table with the name CSN_USER . Here , we're importing the table CSN.CSN_USER into the schema USER2

imp file=csn_user_2409.dmp log=imp_csn_user_2409.log fromuser=csn touser=user2 tables=CSN_USER

Username: / as sysdba


Step 3 : Now rename the table ( of USER2 schema )

sqlplus user2/user2

SQL > alter table user2.CSN_USER rename to csn_user_241009 ;


Step 4 : Now take the export of the table from USER2 schema

exp file=exp_csn_user_2409.dmp log=exp_csn_user_2409.log tables=user2.csn_user_241009

Username: / as sysdba

Step 5 : Import the table into required schema ( that's CSN )


imp file=exp_csn_user_2409.dmp log=imp_exp_csn_user_2409.log fromuser=user2 touser=csn tables=csn_user_241009

Username: / as sysdba