Square DBA – Oracle DBA Talk

Move and partitioned index to a new tablespace

To dynamically move a partitioned index to a new tablespace run the following SQL

select ‘Alter index ‘||INDEX_OWNER||’.'||INDEX_NAME||’
rebuild partition ‘||PARTITION_NAME||’ tablespace CDR_DETAIL_INDEX1;’
from dba_ind_partitions where TABLESPACE_NAME = ‘DW_CDR_DETAIL’;

ORA-01502: index or partition of such index is in unusable state

Do you ever get and ORA-01502? This error code says your index is in an unusable state. It’s easy to find these indexes.

select index_name, status, owner from
dba_indexes where STATUS = 'UNUSABLE';

If you want to build the rebuild SQL that is easy too.

select 'alter index '||owner||'.'||index_name||' rebuild;' from
dba_indexes where STATUS = 'UNUSABLE';