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 >

ORA-29342: user APPS does not exist in the database ; During transport tablesapce option

[plantopiadb:oraperf] /home/users/oraperf> imp transport_tablespace=y file=/perf/dump/perfdump.dmp log=/perf/dump/perfdump>

Import: Release 9.2.0.7.0 - Production on Fri Sep 11 15:45:06 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
"BEGIN sys.dbms_plugts.checkUser('APPS'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user APPS does not exist in the database
ORA-06512: at "SYS.DBMS_PLUGTS", line 1594
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully



Soltuion :

APPS user is having object in respective tablespace (PERFSTAT in our case) at the source database. But no APPS user at the destination database. Use the query mentioned below to fine all the users having the object in PERFSTAT tablespace at source database and need to be created at the destination database , in case if not present .

SQL> select distinct owner from dba_segments where tablespace_name='PERFSTAT' ;

OWNER
------------------------------
APPS
BIZMAX
DBSNMP
KIC
PERFSTAT
TRCANLZR

create all the users in your destiantion database . Where you're plugin the tablespace ..

ORA-29335: tablespace 'PERFSTAT' is not read only ( during transport tablespace option )

[veritas:oraprod] /home/oraprod > TAT file=/u43/oradata/stats_dump/perfdump.dmp log=/u43/oradata/stats_dump/perfdump.log <

Export: Release 9.2.0.5.0 - Production on Fri Sep 11 12:44:26 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: / as sysdba

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses UTF8 character set (possible charset conversion)

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

EXP-00008: ORACLE error 29335 encountered

ORA-29335: tablespace 'PERFSTAT' is not read only

ORA-06512: at "SYS.DBMS_PLUGTS", line 436

ORA-06512: at line 1

EXP-00000: Export terminated unsuccessfully

Soltuion :

This is because of the tablespace in READ WRITE mode . Before taking the export of metadata, tablespace

needs to be taken in READ ONLY mode