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.