Overview

Method

Usage Example

et linesize 110 
set verify   off 
set pagesize 40 
 
 
column owner format a5 
column r_owner format a5 
column column_name format a12 
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 
/ 
 
set verify on 
 
clear columns 
 
undef owner 
undef table_name