Search notes:

Oracle: SQL query to recursively query referential integrity dependencies

An SQL statement to recursively query primary foreign key (referential) integrity dependencies: 
with  p_r as (
   select
      p.table_name             p_table_name,
      p.owner                  p_owner,
      p.constraint_name        p_constraint_name,
      r.table_name             r_table_name,
      r.owner                  r_owner,
      r.constraint_name        r_constraint_name
   from
      all_constraints p                                                join
      all_constraints r on p.owner           = r.r_owner        and
                           p.constraint_name = r.r_constraint_name
),
rec (table_name, owner /*, p_constraint_name, r_table_name, r_owner, r_constraint_name */, lvl) as (
   select
    'TQ84_START'     table_name,
    'RENE'           owner,
     0               lvl
   from
      dual
UNION ALL
   select
      p_r.r_table_name     ,
      p_r.r_owner          ,
      rec.lvl + 1
   from
      rec                                                         join
      p_r on
          p_r.p_table_name      = rec.table_name and
          p_r.p_owner           = rec.owner
)
cycle table_name set cycle_ to '1' default '0'
select
   rpad(' ', 2*lvl) || rec.table_name table_name,
   owner
from
   rec;

Test data

create table tq84_start(id number primary key, nn number not null);
create table tq84_lvl_2_A(id number primary key,                            id_start references tq84_start);
create table tq84_lvl_2_B(id number primary key, uq number unique not null, id_start references tq84_start);
create table tq84_lvl_3_C(id number primary key, id_lvl_2_a references tq84_lvl_2_A);
create table tq84_lvl_3_D(id number primary key, id_lvl_2_b references tq84_lvl_2_B);
create table tq84_lvl_3_E(id number primary key, id_lvl_2_b references tq84_lvl_2_B(uq));
Add a record that causes the dependencies to have a cycle: 
alter table tq84_start add (id_lvl_3_e references tq84_start);
When queried with the statement, the result is: 
TQ84_START        RENE
  TQ84_LVL_2_A    RENE
  TQ84_LVL_2_B    RENE
    TQ84_LVL_3_C  RENE
    TQ84_LVL_3_D  RENE
    TQ84_LVL_3_E  RENE

Cleaning up

alter table tq84_lvl_3_e disable primary key;
drop  table tq84_lvl_3_e;
drop  table tq84_lvl_3_d;
drop  table tq84_lvl_3_c;
drop  table tq84_lvl_2_b;
drop  table tq84_lvl_2_a;
drop  table tq84_start;

Index