-- #############################################################################################
--
-- %Purpose: Show Partition Indexes (DBA_IND_COLUMNS, DBA_INDEXES)
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
set feed off;
set pagesize 100000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;

spool show_part_indexes.lst
set pause off

column index_name format a25 wrap heading 'Index|Name'
column uni format a5 heading 'Uniq-|ness'
column table_name noprint new_value tab
column column_name format a30 heading 'Column|Name'
column table_owner format a10 heading 'Table|Owner'

ttitle left 'Partitioned Indexes of Table: 'tab skip 2

break on table_name skip page -
on table_owner skip 2 -
on index_name skip

select i.table_owner,
       i.index_name,
       c.table_name,
       c.column_name,
       decode(i.uniqueness,'UNIQUE','YES','NONUNIQUE','NO','???') uni
  from dba_ind_columns c, dba_indexes i
 where i.table_name = c.table_name
   and i.index_name = c.index_name
   and i.table_name like upper('%')
   and i.table_owner = c.table_owner
   and i.partitioned = 'YES'
order by i.table_owner,
         c.table_name,
         i.uniqueness desc,
         c.index_name,
         c.column_position;
spool off;
set feed on echo off termout on pages 24 verify on;
ttitle off;