Sunday, March 26, 2006

Move Oracle Indexes To Another Tablespace

Some (mostly old-school) Oracle DBAs like to move indexes into a tablespace other than the tablespace(s) for tables. Moving an index does rebuild the index, for the limited cases when an index rebuild is a good thing.
This SQL will generate the SQL to move indexes.

select 'alter index ' || owner || '.' || index_name || ' rebuild
tablespace &newTablespace'
from sys.dba_indexes
where owner = '&ownerName'
/

No comments:

Post a Comment