Monday, August 17, 2009

- Reorg IOT Tables starting with SYS_IOT_OVER

- 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: