Reorg Index

4) Index

- Normal

Alter index rebuild tablespace ;

- IOT Indexes starting with SYS_IOT_TOP

If we move the IOT indexes with normal commnad, we get the following error

ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

To move the IOT indexes , use the following method

a) Find the table name on which index is created

SQL> select table_name,table_owner from dba_indexes where index_name like 'SYS_IOT_TOP_375805';

TABLE_NAME TABLE_OWNER
------------------------------ ------------------------------
AQ$_WF_WS_SAMPLE_G APPLSYS


b) If the table name is starting with AQ$

1)Find the queue table, in the same way as was done above for tables.


SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_WS_SAMPLE%';

QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_WS_SAMPLE APPLSYS

We have removed the characters before first underscore and after last underscore
When searching for queue table.

2) exec move_aqt.move('APPLSYS',' WF_WS_SAMPLE' ,'DUMMY');

Package move_aqt needs to be installed as mentioned above.
3) Index SYS_IOT_TOP_375805 will be moved to dummy tablespace.

If the table name is not starting with AQ$

1) Move the table_name , from step (a), this will move the index.


5) Index Partition

Alter index rebuild partition
tablespace ;

dba_ind_partitions -- to get the partition name

6) Index Subpartition

Alter index rebuild subpartition
tablespace ;

Dba_ind_subpartitions - to get the subpartition name