The Geek Diary

How to Move tables and indexes to a different tablespace

by

Today, I got a request from the application team to separate tables & indexes on tablespace level. We can move the indexes & tables to separate tablespaces using the below query.

ALTER TABLE [TABLE_NAME] MOVE TABLESPACE TEST_TBL;
ALTER INDEX [INDEX_NAME] REBUILD TABLESPACE TEST_TBL;

Above queries are possible to move 1 to 2 indexes & tables, but in application schema, there might be 1000’s of tables & indexes. To move all these we can not write the statement for each & every table & index.

We can use dynamic queries to achieve this.

I have created a new tablespace INDX_TB for moving the indexes, so we need to only move indexes as tables will be in originally created tablespace.

Here we can move the indexes using online option. If you wish to move tables also to some different tablespace, we can create a new tablespace for tables & write a script to move the tables.

spool index_rebuild_prod.sql
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE PROD_DATA;' from DBA_TABLES WHERE OWNER ='PROD';
spool off;
spool table_move_prod.sql
select 'ALTER INDEX ' || OWNER || '.'|| index_name ||' rebuild tablespace INDX_TB online;' from dba_indexes where owner='PROD';
spool off;

After creating the dynamic script, we have to execute the scripts.

For index movement:

SQL> spool index_rebuild_prod.log
SQL>@/home/oracle/index_rebuild_prod.sql
SQL> spool off;

For Table movement:

SQL> spool table_move_prod.log
SQL>@/home/oracle/table_move_prod.sql
SQL> spool off;

These scripts can be executed by sys or other users having dba privileges. Once the tables & indexes are moved to another tablespace don’t forget to update the statistics on the schema.

SQL> EXEC DBMS_STATS.gather_schema_stats('PROD', estimate_percent => 25, cascade => TRUE);

Final Thoughts

There have been many discussions about whether rebuilding indexes is useful or not. Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.

The most common justifications given for rebuilding an index are:

  • index becomes fragmented
  • index grows and grows – deleted space is not re-used
  • index clustering factor becomes out of sync

In fact, most indexes remain both balanced and fragmentation-free because free leaf entries will be reused. Inserts/Updates and Deletes result in free slots being scattered around the index blocks, but these will typically be refilled. The clustering factor reflects how sorted the table data is with respect to the given index key. Rebuilding an index never has an influence on the clustering factor but instead requires a table re-organization.

You May Also Like