set lines 256 pages 50

col TABLE_OWNER               for a25                  heading "Owner"
col TABLE_NAME                for a30                  heading "Table"
col OWNER                     for a25                  heading "Owner"
col INDEX_NAME                for a30                  heading "Index"
col INDEX_TYPE                for a10                  heading "Type"
col PARTITIONED               for a3                   heading "Par"
col PARTITION_POSITION        for 999                  heading "P#"
col PARTITION_NAME            for a30                  heading "Partition"
col SUBPARTITION_NAME         for a30                  heading "Subpartition"
col PART_HIGHVAL              for a32     trunc        heading "Partition|high value"
col PART_INTERVAL             for a3                   heading "Par|Int"
col SUBPART_HIGHVAL           for a32     trunc        heading "Subpartition|high value"
col SUBPART_INTERVAL          for a3                   heading "Sub|Int"
col NUM_ROWS                                           heading "Rows"
col DISTINCT_KEYS                                      heading "Dist|keys"
col CLUSTERING_FACTOR                                  heading "CLUF"


define v_table_owner=&&1
define v_table_name=&&2

prompt
prompt Table
prompt =====

select
     par.TABLE_OWNER
    ,par.TABLE_NAME
        ,par.PARTITION_POSITION
    ,par.PARTITION_NAME
    ,par.NUM_ROWS
    ,par.HIGH_VALUE PART_HIGHVAL
    ,par.INTERVAL PART_INTERVAL
    ,sub.SUBPARTITION_NAME
    ,sub.NUM_ROWS
    ,sub.HIGH_VALUE SUBPART_HIGHVAL
    ,sub.INTERVAL SUBPART_INTERVAL
from
        dba_tab_partitions par
    left outer join dba_tab_subpartitions sub
        on (par.TABLE_OWNER=sub.TABLE_OWNER) and (par.TABLE_NAME=sub.TABLE_NAME) and(par.PARTITION_NAME=sub.PARTITION_NAME)
where
        par.table_owner='&&v_table_owner'
        and par.table_name='&&v_table_name'
order by
         par.PARTITION_POSITION asc
        ,sub.SUBPARTITION_POSITION asc
;

prompt Index
prompt =====

select
     ind.OWNER
    ,ind.INDEX_NAME
    ,ind.INDEX_TYPE
    ,ind.STATUS
    ,ind.PARTITIONED
    ,ind.NUM_ROWS
    ,ind.DISTINCT_KEYS
    ,ind.CLUSTERING_FACTOR
    ,indpar.PARTITION_NAME
    ,indpar.NUM_ROWS
    ,indpar.DISTINCT_KEYS
    ,indpar.CLUSTERING_FACTOR
    ,indsubpar.SUBPARTITION_NAME
    ,indsubpar.NUM_ROWS
    ,indsubpar.DISTINCT_KEYS
    ,indsubpar.CLUSTERING_FACTOR
from
    dba_tables tab
    left join dba_indexes ind on (tab.TABLE_NAME=ind.TABLE_NAME)
    left outer join dba_ind_partitions indpar on (ind.OWNER=indpar.INDEX_OWNER and ind.INDEX_NAME=indpar.INDEX_NAME)
    left outer join dba_ind_subpartitions indsubpar on (ind.OWNER=indsubpar.INDEX_OWNER and ind.INDEX_NAME=indsubpar.INDEX_NAME and indpar.PARTITION_NAME=indsubpar.PARTITION_NAME)
where
        tab.owner='&&v_table_owner'
    and tab.table_name='&&v_table_name'
order by
     ind.INDEX_NAME asc
    ,indpar.PARTITION_POSITION asc
    ,indsubpar.SUBPARTITION_POSITION asc
;