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;
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