/* ************************************************ */
/*                                                  */
/* format the report:                               */
/*        DBAIND2 - Index Column Report             */
/*                                                  */
/* name:    @c:\oracle\sql\dbaind2.sql              */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

set feedback  off
set WRAP      off
set pagesize  58
set linesize  124 
set space     5
set newpage   0
set trimout   on
set verify    on
set underline =
set echo      off
set termout   off

whenever sqlerror exit failure
spool dbaind2.lst

ttitle left 'PAGE:' sql.pno format 999 - 
center 'DBAIND2 - Index Column Report' -
right CWS_DATE skip 2

 column index_owner                     format A8  HEADING 'OWNER'
 column index_name			format A30 heading 'INDEX'
 column uniqueness                      format A10 HEADING 'UNIQUE/NON'
 column table_name			format A30 heading 'TABLE'
 column column_name			format A30 heading 'COLUMN'

 break   on  index_owner skip page on index_name skip 1 on -
      uniqueness on table_name 

select index_owner,
     c.index_name,
     uniqueness,
     c.table_name,
     COLUMN_NAME
from sys.dba_ind_columns c, 
     sys.dba_indexes i
where (( c.table_name = 'MTL_UNITS_OF_MEASURE' 
        or c.table_name = 'MTL_UOM_CONVERSIONS')
    	and c.index_name = i.index_name 
    	and c.index_owner = i.table_owner )
order by index_owner, 
     c.index_name, 
     column_position;

spool off
exit
