REM Script to show the structure of partitioned indices

set pages 500;
set lines 200;
set feed off;

–column index_name noprint;
column partition_position noprint;
column high_value format A20;

column partition_name format A22;
column tablespace_name format A15;
column fl format 99;
column flg format 99;

prompt ===================================================================================================

prompt Partitioned indices: general layout

select
index_name,partition_position,partition_name,
tablespace_name,high_value,pct_free,initial_extent,
freelists fl, freelist_groups flg,logging
from dba_ind_partitions
order by index_name,partition_position;

prompt ===================================================================================================

prompt Partitioned indices: usage stats

select
a.index_name,a.partition_position,a.partition_name,
a.num_rows,
b.blocks,b.extents,a.last_analyzed
from dba_ind_partitions a, dba_segments b
where
a.index_owner = b.owner and
a.partition_name = b.partition_name
order by index_name,partition_position;

prompt ===================================================================================================

set feed on;