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:
Post a Comment