Moving a table from one tablespace to another

There are couple of ways in which a table can be moved to a different tablespace:

  1.  a) One of them is to perform export/import
  2. 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

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: