/* List all keys/indexes in a particular table */

SELECT   a.index_name, a.column_name, b.uniqueness, a.column_position
  FROM   user_ind_columns a, user_indexes b
WHERE    a.index_name = b.index_name 
  AND    a.table_name = 'CLASS'         -- for table 'CLASS', uppercase. 
ORDER BY 1, 4;



/* List all keys/indexes/foreign keys and their referenced tables  */ 

SELECT    tbl.table_name, tbl.index_name, tbl.constraint_name, tbl.constraint_type,
          ref.table_name "referenced table"
FROM      user_constraints tbl 
LEFT JOIN user_constraints ref                          -- self join 
       ON tbl.r_constraint_name = ref.constraint_name
WHERE     tbl.constraint_type in('P','U','R')           -- Primary, Unique, Reference
ORDER BY  tbl.table_name, tbl.constraint_type;



/* List all keys/indexes/foreign keys and their referenced tables & columns */ 

SELECT    ucc1.table_name, ucc1.column_name, ucc1.position,
      uc.constraint_name, decode(uc.constraint_type,'P','PK','R','FK','U','Uniq') as type, 
      ucc2.table_name, ucc2.column_name, ucc2.position
FROM      user_constraints uc 
JOIN      user_cons_columns ucc1 on uc.constraint_name   = ucc1.constraint_name
LEFT JOIN user_cons_columns ucc2 on uc.r_constraint_name = ucc2.constraint_name 
WHERE     uc.constraint_type in('P','R','U')
ORDER BY  1, 4, 5, 3, 2;



/* List all keys/indexes/foreign keys and their referenced tables & columns */ 
/* Another way */

SELECT  tbl.table_name, tbl.index_name, tbl.constraint_name, 
    DECODE(tbl.constraint_type, 'P','Primary','R','Foreign Key','U','Unique') as TYPE,
    tbl.column_name, tbl.position,
    ref.table_name "referenced table", ref.column_name "referenced column"
FROM
    (SELECT uc.*, ucc.column_name, ucc.position         -- inline view
       FROM user_constraints uc, user_cons_columns ucc
      WHERE uc.constraint_name = ucc.constraint_name
        AND uc.constraint_type in ('P','R','U') ) tbl
LEFT JOIN
    (SELECT uc.*, ucc.column_name, ucc.position         -- inline view
       FROM user_constraints uc, user_cons_columns ucc
      WHERE uc.constraint_name = ucc.constraint_name
        AND uc.constraint_type in ('P','R','U') ) ref
     ON  tbl.r_constraint_name = ref.constraint_name
ORDER BY 1, 2, 3, 4, 5, 6;