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
