Tuesday, September 6, 2011

Moving a table from one tablespace to another


There are couple of ways in which a table can be moved to a different tablespace:
   a) One of them is to perform export/import 
   b) Another is to use ‘ALTER TABLE’ command with ‘MOVE tablespace’ clause

If you move a table from one tablespace to another, you need to REBUILD the Related indexes if those indexes are on same tablespace

      1)  Check indexes for a table

SQL> SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME = ‘ tab_name’;

Index_name       table_name           status
-----------------       ------------------       ----------
Ind_name            tab_name            valid

2)     Check the tablespace in which our table is located

SQL> select tablespace_name,table_name from user_tables where table_name=’TAB_NAME’;


3)    Now moving to another tablesapce

SQL>  ALTER TABLE tab_name MOVE TABLESPACE new_tablespace;


4)    Now check the status of the index again

SQL> SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME = ‘ tab_name’;

Index_name       table_name           status
-----------------       ------------------       ----------
Users                 tab_name             Unusable
                                                                              

5)    Rebuild the index in order to make the index valid

SQL>  ALTER INDEX ind_name REBUILD;
 index altered


6)    Check the status of the index again

SQL> SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME = ‘ tab_name’;

Index_name       table_name           status
-----------------       ------------------       ----------
ind_name               tab_name           valid

0 comments:

Post a Comment

Auto Scroll Stop Scroll