Translate

Monday 14 November 2011

How to move an IOT into another tablespace?

SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
SYS_IOT_OVER_71142 TABLE TS_D01
SYS_IOT_TOP_71142 INDEX TS_D01
Let's assume you want to move all the segments from their current tablespace to another tablespace. You'll quickly discover that you cannot use the standard commands:
SQL> ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01;

ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

SQL> ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01;

ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
You have to first find the name of the associated IOT table:
SQL> SELECT iot_name FROM user_tables WHERE table_name = 'SYS_IOT_OVER_71142';

IOT_NAME
------------------------------
MY_IOT

SQL> SELECT table_name FROM user_indexes WHERE index_name = 'SYS_IOT_TOP_71142';

TABLE_NAME
------------------------------
MY_IOT

Then you can move the segments:
SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_i01;

Table altered.

SQL> ALTER TABLE my_iot MOVE OVERFLOW TABLESPACE ts_i01;

Table altered.

SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
SYS_IOT_OVER_71142 TABLE TS_I01
SYS_IOT_TOP_71142 INDEX TS_I01
The first statement moves the top segment and the second one the overflow segment. This can be done in one statement using:
SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_d01 OVERFLOW TABLESPACE ts_d01;

Table altered.

No comments:

Post a Comment