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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *