There was a question on OTN DB general on how to move an IOT ( index organized table ) from one tablespace to another.
Pretty easy answer, but I realized I had never done it and was assuming it was a simple MOVE command. So what the heck, I am sick and tired of reading old emails and have finished laughing over the Chronicles of George. So lets go see what damage I can do and possibly learn something.
SQL> SET LINES 132
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for HPUX: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
SQL> --CREATE TABLESPACE TEST1 DATAFILE 'namehere' SIZE 16M ;
SQL> --CREATE TABLESPACE TEST2 DATAFILE 'namehere' SIZE 16M;
SQL> DROP TABLE TIOT;
Table dropped.
SQL> CREATE TABLE TIOT
2 (ID NUMBER
3 ,DATA VARCHAR2(30)
4 ,CONSTRAINT IDPK PRIMARY KEY(ID)
5 )
6 ORGANIZATION INDEX
7 TABLESPACE TEST1
8 OVERFLOW TABLESPACE TEST2
9 ;
Table created.
SQL> INSERT INTO TIOT (ID,DATA) VALUES (1,'A');
1 row created.
SQL> INSERT INTO TIOT (ID,DATA) VALUES (2,'B');
1 row created.
SQL> INSERT INTO TIOT (ID,DATA) VALUES (3,'C');
1 row created.
SQL> INSERT INTO TIOT (ID,DATA) VALUES (4,'D');
1 row created.
SQL> INSERT INTO TIOT (ID,DATA) VALUES (5,'E');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TIOT;
ID DATA
---------- ------------------------------
1 A
2 B
3 C
4 D
5 E
SQL> SELECT IOT_TYPE,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='TIOT';
IOT_TYPE TABLESPACE_NAME
------------ ------------------------------
IOT
SQL> SELECT SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDPK';
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
INDEX TEST1
SQL> SELECT INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDPK';
INDEX_TYPE TABLE_NAME TABLESPACE_NAME STATUS
--------------------------- ------------------------------ ------------------------------ --------
IOT - TOP TIOT TEST1 VALID
SQL> ALTER TABLE TIOT MOVE TABLESPACE TEST2;
Table altered.
SQL> SELECT * FROM TIOT;
ID DATA
---------- ------------------------------
1 A
2 B
3 C
4 D
5 E
SQL> SELECT IOT_TYPE,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='TIOT';
IOT_TYPE TABLESPACE_NAME
------------ ------------------------------
IOT
SQL> SELECT SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDPK';
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
INDEX TEST2
SQL> SELECT INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDPK';
INDEX_TYPE TABLE_NAME TABLESPACE_NAME STATUS
--------------------------- ------------------------------ ------------------------------ --------
IOT - TOP TIOT TEST2 VALID
SQL> INSERT INTO TIOT (ID,DATA) VALUES (99,'Z');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TIOT;
ID DATA
---------- ------------------------------
1 A
2 B
3 C
4 D
5 E
99 Z
6 rows selected.
SQL> ALTER TABLE TIOT MOVE TABLESPACE TEST1 OVERFLOW TABLESPACE TEST1;
Table altered.
SQL> SELECT * FROM TIOT;
ID DATA
---------- ------------------------------
1 A
2 B
3 C
4 D
5 E
99 Z
6 rows selected.
SQL> SPOOL OFF
