Overview

Method

Query

set linesize 110
set pagesize 40
set verify off

column owner format a5
column r_owner format a5
column column_name format a12
column table_name format a15
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15
column position heading P format 9
column tt off

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
/

set verify on
clear columns
undef owner
undef table_name