Doc

    Name:   forgnkey.sql

    Author: Mark Gurry

    The scripts below provide information on foreign key
    usage. The first script lists the foreign keys and the
    second lists foreign keys that are missing indexes on
    the foreign key columns in the child table. If the index
    is not in place, share lock problems may occur on the 
    parent table.

# 
ttitle 'Foreign Key Listing'
SELECT  a.owner , a.table_name , c.column_name ,
        b.owner , b.table_name , d.column_name 
FROM    dba_constraints a, dba_constraints b,
        dba_cons_columns c, dba_cons_columns d
WHERE   a.r_constraint_name = b.constraint_name
  AND   a.constraint_type = 'R'
  AND   b.constraint_type = 'P'
  AND   a.r_owner=b.owner
  AND   a.constraint_name = c.constraint_name
  AND   b.constraint_name=d.constraint_name
  AND   a.owner = c.owner
  AND   a.table_name=c.table_name
  AND   b.owner = d.owner
  AND   b.table_name=d.table_name;

ttitle 'Foreign Keys with Indexes Missing on Child Table' 
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
        ||'['||acc.position||'])'||' ***** Missing Index'
  FROM  all_cons_columns acc, all_constraints ac
 WHERE  ac.constraint_name = acc.constraint_name
   AND  ac.constraint_type = 'R'
   AND  (acc.owner, acc.table_name, acc.column_name, acc.position) 
             IN
 (SELECT acc.owner, acc.table_name, acc.column_name, acc.position 
    FROM   all_cons_columns acc, all_constraints ac
   WHERE  ac.constraint_name = acc.constraint_name
     AND   ac.constraint_type = 'R'
  MINUS
  SELECT table_owner, table_name, column_name, column_position
    FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name, 
         acc.column_name, acc.position;