Wednesday, 3 December 2014

To know the status of sub partition indexes- all_ind_subpartitions status UNUSABLE or N/A in dba_indexes


If index status is N/A in dba_indexes then its partition index and If status is  N/A in dba_ind_partitions then its sub partition index Check in all_ind_subpartitions.


SELECT a.index_name, b.table_name, a.partition_name, c.index_type, c.global_stats, d.subpartition_name, d.status
FROM dba_IND_PARTITIONS a
, dba_TAB_PARTITIONS b
, dba_INDEXES c
, all_ind_subpartitions d
WHERE a.partition_name= b.partition_name
AND c.index_name=a.index_name
AND c.table_name = b.table_name
and a.partition_name= d.partition_name
AND a.index_name in (select index_name from all_part_indexes where table_name = '&table_name' )
AND b.table_name= '&table_name';

No comments: