with
tmp_hier1 as
((select
table_name as child, null as parent from user_tables
minus
select
distinct table_name as child, null as parent from user_constraints where
constraint_type = 'R')
union
select distinct table_name as child,
(select distinct table_name from user_constraints ac1 where
ac1.constraint_name=ac.r_constraint_name) as parent
from user_constraints
ac where constraint_type = 'R'),
tmp_hier2 as
(select child, parent,
LEVEL as lvl
from tmp_hier1
START WITH parent is null
connect
by nocycle prior child = parent
order by lvl, child)
select child,
max(lvl)
from tmp_hier2
group by child
order by max(lvl),
child;
