Query below lists:

(A) all unique indexes, along with their columns, on objects accessible to the current user in Oracle database

(B) all unique indexes, along with their columns, on objects in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select ind.index_name,
       LISTAGG(ind_col.column_name, ',')
            WITHIN GROUP (order by ind_col.column_position) as columns,
       ind.index_type,
       ind.table_owner as schema_name,
       ind.table_name as object_name,
       ind.table_type as object_type
from sys.all_indexes ind
inner join sys.all_ind_columns ind_col
           on ind.owner = ind_col.index_owner
           and ind.index_name = ind_col.index_name
where ind.uniqueness = 'UNIQUE'
-- excluding some Oracle maintained schemas
and ind.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
group by ind.index_name,
         ind.index_type,
         ind.table_owner,
         ind.table_name,
         ind.table_type
order by ind.table_owner,
         ind.table_name,
         ind.index_name;

B. If you have privilege on dba_indexes and dba_ind_columns

select ind.index_name,
       LISTAGG(ind_col.column_name, ',')
            WITHIN GROUP (order by ind_col.column_position) as columns,
       ind.index_type,
       ind.table_owner as schema_name,
       ind.table_name as object_name,
       ind.table_type as object_type
from sys.dba_indexes ind
inner join sys.dba_ind_columns ind_col
           on ind.owner = ind_col.index_owner
           and ind.index_name = ind_col.index_name
where ind.uniqueness = 'UNIQUE'
-- excluding some Oracle maintained schemas
and ind.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
group by ind.index_name,
         ind.index_type,
         ind.table_owner,
         ind.table_name,
         ind.table_type
order by ind.table_owner,
         ind.table_name,
         ind.index_name;

Columns

• index_name - index name
• columns - list of columns index is on separated by ","
• index_type - index type
• schema_name - indexed object's owner, schema name
• object_name - indexed object's name
• object_type - indexed object's type

Rows

• One row represents one index in a database
• Scope of rows: (A) all unique indexes, along with their columns, on objects accessible to the current user in Oracle database, (B) all unique indexes, along with their columns, on objects in Oracle database
• Ordered by schema name, object name, index name, index column sequence number

Sample results