IOT related table, index and overflow segment (ORA-28650,ORA-25191) rebuilt issue

Move IOT related table, index and overflow segment to another tablespace

1. Primary index for IOT table can not be rebuilt

SQL> alter index EDWREP.C_S_PK rebuild tablespace APPS_TS_OATM_MIG;
alter index EDWREP.C_S_PK rebuild tablespace APPS_TS_OATM_MIG
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

2. Find out the table name for this IOT table

SQL> select index_type, table_name from dba_indexes where index_name=’C_S_PK’;

INDEX_TYPE  TABLE_NAME
----------  --------------------------
IOT         CLASSIFICATION_STORE


3. Move the table instead rebuild the index

SQL> alter table EDWREP.CLASSIFICATION_STORE move tablespace APPS_TS_OATM_MIG;
Table altered.

4. Overflow segment also can not be moved directly by referencing the Overflow table name

SQL> alter table EDWREP.SYS_IOT_OVER_57745 move tablespace APPS_TS_OATM_MIG;
alter table EDWREP.SYS_IOT_OVER_57745 move tablespace APPS_TS_OATM_MIG
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

5. Find out the IOT_NAME for the overflow table

SQL> select iot_name from dba_tables where table_name = 'SYS_IOT_OVER_57745';
IOT_NAME
--------------------
RESERVATION_STORE

6. Using the IOT_NAME with ‘alter table .. move overflow ..’ to move overflow segment

SQL> alter table edwrep.RESERVATION_STORE move overflow tablespace APPS_TS_OATM_MIG;
Table altered.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.