Dynamic Query for Generating REBUILD INDEX Command
With the article “Dynamic Query for Generating REBUILD INDEX Command,” we provide 2 dynamic queries through which we will have the possibility to create the rebuild command for indexes and partitioned indexes that are in the UNUSABLE state.
REBUILD is the reconstruction of the index. This operation is performed when the DDL of the table is modified, to reclaim unused space, but above all, to eliminate fragmentation within the index.
Therefore, it is vitally important to keep the fragmentation status of tables and indexes in a schema under control. If they are too fragmented, performance degradation would be inevitable.
During the REBUILD phase, the table must not be accessed, as the rebuild would fail with the error ORA-00054: resource busy and acquire with NOWAIT specified.
If the REBUILD is executed with the ONLINE clause, the data remains available to users. This is because, with the classic REBUILD, an EXCLUSIVE LOCK is set to block all DML operations on the affected object. With the latest Oracle releases, the LOCK period has been reduced, allowing access to the data as well.
Below are the statements:
Dynamic Query for Generating REBUILD INDEX Command
SET LINES 170
select 'alter index '||i.OWNER||'.'||i.INDEX_NAME||' REBUILD;'
from dba_indexes i, dba_segments s
where i.OWNER not in ('SYS','SYSTEM')
and i.OWNER = s.OWNER
and i.INDEX_NAME = s.SEGMENT_NAME
and i.STATUS <>'VALID';
Construction of REBUILD INDEX PARTITION statement
SET LINES 170
select 'alter index '||i.index_OWNER||'.'||i.INDEX_NAME||' REBUILD PARTITION ' ||s.PARTITION_NAME|| ';'
from dba_ind_partitions i, dba_segments s
where i.INDEX_OWNER not in ('SYS','SYSTEM')
and i.INDEX_OWNER = s.OWNER
and i.INDEX_NAME = s.SEGMENT_NAME
and i.partition_name = s.partition_name and i.STATUS <> 'VALID';To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin
For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.
0 Comments