Tuesday, March 24, 2020

How do you move an index organized table?



The following index needs to be moved from tablespace DATA1 to tablespace DATA2:

select unique segment_name,segment_type,tablespace_name 
from dba_segments 
where owner='SCOTT'
and tablespace_name = 'DATA1'
;
Result:
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
COUNTRY_ID_PK INDEX DATA1

Turns out, the index belongs to an IOT:

select i.owner,i.table_name,i.tablespace_name,i.status, t.iot_type
from dba_indexes i join dba_tables t
on (i.table_name = t.table_name)
and t.table_name ='COUNTRIES'
and t.owner=i.owner
where i.index_name='COUNTRY_ID_PK'
and i.owner='SCOTT';
Result in:

OWNER TABLE_NAME TABLESPACE_NAME STATUS IOT_TYPE
SCOTT COUNTRIES DATA1 VALID IOT

If you try to move the index COUNTR_ID_PK with the usual "alter index ... rebuild" clause, it will fail with
ORA-28650: Primary index on an IOT cannot be rebuilt

Instead, move the table. Since it's an IOT, the index will be moved automatically with it:
alter table SCOTT.COUNTRIES move tablespace DATA2 ONLINE;

Verify that the IOT was moved by executing the same query as previously:

OWNER TABLE_NAME TABLESPACE_NAME STATUS IOT_TYPE
SCOTT COUNTRIES DATA2 VALID IOT


No comments:

Post a Comment