- IOT Tables starting with SYS_IOT_OVER
If we move the above table with normal command, we get the following error
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
To move the above table , use following method
a) Find the Iot_name
select OWNER,TABLE_NAME,TABLESPACE_NAME,IOT_NAME,IOT_TYPE
from dba_tables
where TABLE_NAME like 'SYS_IOT_OVER_375778';
OWNER TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------
APPLSYS SYS_IOT_OVER_375778 APPS_TS_QUEUES AQ$_WF_CONTROL_G IOT_OVERFLOW
b) Find the queue table
Queue_table - remove the characters before the first underscore and last underscore of IOT_NAME
And then search for the queue table
So in our example IOT_NAME is AQ$_WF_CONTROL_G
So we are searching queue_table like '%WF_CONTROL%'
SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_CONTROL%';
QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_CONTROL APPLSYS
c) Install move_aqt package using Note 394713.1
d) exec move_aqt.move('APPLSYS',WF_CONTROL,'DUMMY');
e) Once this is done table SYS_IOT_OVER_375778 will be moved to DUMMY tablespace.
- Long Datatye column
While moving the tables with column of long datatype, we get the following error
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
These tables cannot be moved using normal move command.
We need to export, drop and import the tables.
No comments:
Post a Comment