List foreign keys to and from a given table

set linesize 200
set verify off
set pagesize 40

break on owner on table_name on constraint_name on r_constraint_name

column owner format a10
column r_owner format a10
column column_name format a15
column tt noprint
column position heading P format 9
column table_name format a15
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15

select
a.tt,
a.owner,
b.table_name,
a.constraint_name,
b.column_name,
b.position,
a.r_constraint_name,
c.column_name,
c.position,
c.table_name r_table_name,
a.r_owner
from
(select
owner,
constraint_name,
r_constraint_name,
r_owner,1 tt
from
dba_constraints
where
owner=upper('&&owner')
and table_name=upper('&&table_name')
and constraint_type!='C'
union
select
owner,
constraint_name,
r_constraint_name,
r_owner,2
from
dba_constraints
where
(r_constraint_name,r_owner) in
(select
constraint_name,
owner
from
dba_constraints
where
owner=upper('&owner')
and table_name=upper('&table_name'))
) a,
dba_cons_columns b,
dba_cons_columns c
where
b.constraint_name=a.constraint_name
and b.owner=a.owner
and c.constraint_name=a.r_constraint_name
and c.owner=a.r_owner
and b.position=c.position
order by 1,2,3,4,5
/