Moving a table to a new tablespace on Oracle 10g

On Oracle 10g you can move a table from one tablespace to another as shown below. Keep in mind that everything associated with the table is moved as well (e.g. privileges, constraints, synonyms, etc.), but NOT indexes.

SQL> ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace_name>;

WARNING: By doing so, all indexes associated to the moved table will no longer useful and must be rebuilt as the following:

SQL> ALTER INDEX <index_name> REBUILD;

be available and must be recreated.

Moving an index to a different table space on Oracle 10g

An index can be moved from one tablespace to another as follows:

SQL> ALTER INDEX <index_name> REBUILD TABLESPACE <new_tablespace> [ STORAGE (storage_clause) ];

In the need of moving all indexes from one tablespace to another, the SQL script below can
be used to generate a final SQL script that does all moves sequentially.

set heading off
set feedback off
set linesize 160;
set pagesize 60;

connect system/system

!rm -f rebuildIndexes.sql
spool rebuildIndexes.sql
prompt connect system/system
SELECT ‘alter index ‘ || owner || ‘.’ || index_name ||
‘ rebuild tablespace NEW_TS STORAGE (INITIAL 64K);
FROM all_indexes
WHERE OWNER in (‘AA’, ‘BB’, ‘CC’, ‘DD’) and
TABLESPACE_NAME = ‘MY_TS’ AND PARTITIONED = ‘NO’
ORDER BY NUM_ROWS ASC
/
spool off

The above statement will produce the final SQL script for moving all indexes at once.