Wednesday, July 26, 2006

Moving an IOT

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