Saturday, April 4, 2020

Rebuild All Indexes using 1 SQL | Single query

Below is query to rebuild all indexes in Oracle in one  go. It will generate the dynamic queries as output which can be copy and execute in same SQL window and all indexes will be rebuild automatically.

select
distinct  'alter index "'|| o.owner || '"."' || o.object_name || '" rebuild '
|| decode(i.partition_name, NULL, '', ' PARTITION "'|| i.partition_name ||'"' )
||' online nologging;' as rebuild_list
from
(
select
owner, object_name, subobject_name
from dba_objects
where
(( object_type = 'INDEX PARTITION' and  subobject_name is not null)
OR
(object_type ='INDEX' and  subobject_name is null))
and
created > to_date('1900-01-01-00:00:00','YYYY-MM-DD-HH24:MI:SS')
) o,
(  select id.owner, id.index_name, ip.partition_name
  from
    dba_indexes  id
  where
      'NO' in (ip.logging, id.logging)
    and id.owner = ip.index_owner (+)
    and id.index_name  = ip.index_name (+)) i
where
  o.owner          = i.owner and
  o.object_name    = i.index_name and
  o.subobject_name = i.partition_name;

No comments:

Post a Comment

web stats