-- #############################################################################################
--
-- %Purpose: Show Foreign-Primarykey Relations with Foreign Keys without an Index
--
--           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.
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_foreign_keys.lst
ttitle off;
COLUMN  tab_owner   FORMAT A10 HEADING "Owner";
COLUMN  tab_name_fk FORMAT A15 HEADING "FK-Table";
COLUMN  col_name_fk FORMAT A15 HEADING "FK-Column";
COLUMN  tab_name_pk FORMAT A15 HEADING "PK-Table";
COLUMN  col_name_pk FORMAT A15 HEADING "PK-Column"
--
ttitle left 'Foreign Key Listing' skip 2

break on tab_owner -
on tab_name_fk skip -

SELECT  a.owner tab_owner,
        a.table_name tab_name_fk,
        c.column_name col_name_fk,
        b.table_name tab_name_pk,
        d.column_name col_name_pk
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   a.owner NOT IN ('SYS','SYSTEM')
  AND   b.table_name=d.table_name
  ORDER BY 1,2,3;
--
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
        ||'['||acc.position||'])'||' ***** Missing Index'
        "Indexes Missing on Child Table"
  FROM  all_cons_columns acc, all_constraints ac
 WHERE  ac.constraint_name = acc.constraint_name
   AND  ac.constraint_type = 'R'
   AND  acc.owner NOT IN ('SYS','SYSTEM')
   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;
spool off;
set feed on echo off termout on pages 24 verify on
ttitle off