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