How to Rebuild Unused Indexes or Move Another Tablespace in Oracle

Hi,

Sometimes you need moving tables and indexes to the new tablespace in Oracle.

Especially you want to drop any tablespace so you need to discharge or move all tables and indexes to the new tablespace.

You can move tables,partitions and subpartitions to the another tablespace in Oracle as follows.

https://ittutorial.org/oracle-move-tables-partitions-and-subpartitions-another-tablespace-alter-table-move-tablespace-ora-14257/

You can move lots of indexes to the new tablespace with using generate rebuild scripts.

select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'new_tablespace online parallel 8;' 
from dba_indexes where tablespace_name='PMDB_NDX1';

Above SQL script generates following rebuild scripts.

ALTER INDEX ADMUSER.PK_ACCOUNT REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_PARENT_ACCT REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_DSI REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_UPD REBUILD TABLESPACE new_tablespace online parallel 8;

Rebuild Index Partition generate script is as follows.

set linesize 158;
select 
'alter index '||owner||'.'||segment_name||' rebuild partition '|| partition_name||' tablespace NEW_TABLESPACE;'
from dba_segments where tablespace_name='OLD_TABLESPACE' and segment_type='INDEX PARTITION';

You can run the above script result to rebuild partitions.

Rebuild Index Subpartition generate script is as follows.

set linesize 158;
select 'alter index '||owner||'.'||segment_name||
' rebuild subpartition '|| partition_name||' tablespace NEW_TABLESPACE online;'
from dba_segments where tablespace_name='OLD_TABLESPACE' and segment_type='INDEX SUBPARTITION';

You can run the above script result to rebuild subpartitions.

You can generate and rebuild all Unusable indexes with the following script.

select 'alter index '||owner||'.'||index_name||' rebuild parallel 8;' from dba_indexes where status = 'UNUSABLE'
union all
select 'alter index '||owner||'.'||index_name||' noparallel;' from dba_indexes where status = 'UNUSABLE';

You can generate and rebuild all Unusable partitioned indexes with the following script.

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' parallel 8;' from DBA_IND_PARTITIONS where status = 'UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||' noparallel;' from DBA_IND_PARTITIONS where status = 'UNUSABLE';

You can generate and rebuild all Unusable subpartitioned indexes with the following script.

select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' parallel 8;' from DBA_IND_SUBPARTITIONS where status = 'UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||' noparallel;' from DBA_IND_SUBPARTITIONS where status = 'UNUSABLE';

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/