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

table in read only mode OR restricitng dml on a table

HI ,

I found this somewhere and it's a nice trigger to make a table in read only mode .

16:06:06 > create table test11 (num number(20));

Table created.

16:06:13 > insert into test11 values(1);

1 row created.

16:06:26 > commit;

Commit complete.

16:06:29 > select * from test11;

NUM
----------
1

Now , create the trigger to disable the dml on the table test11

16:06:33 > create or replace trigger test11_read_only
before insert or update or delete
on test11
begin
raise_application_error (-20001, 'RAHUL has restricted DML on this table , Table TEST11 is Read Only ');
end;
/
16:06:56 2 16:06:56 3 16:06:56 4 16:06:56 5 16:06:56 6 16:06:56 7
Trigger created.

16:06:56 > insert into test11 values(1);
insert into test11 values(1)
*
ERROR at line 1:
ORA-20001: RAHUL has restricted DML on this table , Table TEST11 is Read Only
ORA-06512: at "APPS.TEST11_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'APPS.TEST11_READ_ONLY'


16:07:03 >