Moving a table from one tablespace to another

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

A

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