Wednesday, September 10, 2008

Rebuilding Indexes, Index Partitions, Index Subpartitions

# INDEX
select distinct 'alter index bi_mifid_staging.'||segment_name||' rebuild tablespace bi_mifid_stg_data online;'
from dba_segments
where owner = 'BI_MIFID_STAGING'
and segment_type = 'INDEX'
and tablespace_name = 'BI_MIFID_STG_IND';

# INDEX PARTITION
select distinct 'alter index bi_mifid_staging.'||segment_name||' rebuild partition '||partition_name||' tablespace bi_mifid_stg_data online;'
from dba_segments
where owner = 'BI_MIFID_STAGING'
and segment_type = 'INDEX PARTITION'
and tablespace_name = 'BI_MIFID_STG_IND';

# INDEX SUBPARTITIONS
select distinct 'alter index bi_mifid_staging.'||index_name||' rebuild subpartition '||subpartition_name||' tablespace bi_mifid_stg_data online;'
from dba_ind_subpartitions
where index_owner = 'BI_MIFID_STAGING'
and tablespace_name = 'BI_MIFID_STG_IND';