Friday, March 8, 2013

ORA-00997: illegal use of LONG datatype

Our Application team has created objects in the USERS tablespace instead of creating the objects in their dedicated tablespace.

So we need to move the objects from USERS tablespace to their default tablespace.

##########################
#   Errors 
##########################


SQL> alter table AU.TRACTION move tablespace AU_DATA;
alter table AU.TRACTION move tablespace AU_DATA
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


##########################
#   Command Used  
##########################

alter table AU.TRACTION move tablespace AU_DATA;

##########################
#   Informations  
##########################

Table which i tried to move contains LONG datatype. Tables with Data Types with LONG or LONG RAW cannot be moved.

##########################
 Solution 
##########################

  Export & Import The Table. 

SQL> desc AU.TRACTION

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(256)
 DATA                                      NOT NULL LONG


Alter table move will not work. We need to Export & Import the tables. 

No comments: