12june2013 oracle

WITH p AS
  (SELECT c.table_name,
    c.constraint_name,
    o.object_id
  FROM all_constraints c,
    dba_objects o
  WHERE constraint_type='P'
  AND o.object_name    =c.table_name
  AND c.owner          ='SYS'
  AND o.owner          = 'SYS'
  AND o.object_type    ='TABLE'
  AND EXISTS
    (SELECT 1
    FROM all_constraints ref
    WHERE ref.r_constraint_name= c.constraint_name
    AND ref.constraint_type    = 'R'
    )
  ),
  c AS
  (SELECT c.table_name,
    c.R_CONSTRAINT_NAME constraint_name,
    o.object_id
  FROM all_constraints c,
    dba_objects o
  WHERE constraint_type='R'
  AND o.object_name    =c.table_name
  AND c.owner          ='SYS'
  AND o.owner          = 'SYS'
  AND o.object_type    ='TABLE'
  AND EXISTS
    (SELECT 1
    FROM all_constraints ref
    WHERE ref.constraint_name= c.r_constraint_name
    AND ref.constraint_type  = 'P'
    )
  ),
  ORACLE_CONNET_BY_TEST AS
  (SELECT DISTINCT p.object_id table_id,
    0 parent_id ,
    p.table_name
  FROM p,
    c
  WHERE c.table_name!=P.table_name
 
  UNION ALL
 
  SELECT DISTINCT c.object_id table_id,
    p.object_id parent_id ,
    c.table_name
  FROM p,
    c
  WHERE c.constraint_name=p.constraint_name
  ),
  T AS
  (SELECT LEVEL LVL,
    TABLE_ID,
    PARENT_ID,
    LPAD(' ', 4 * (LEVEL - 1))
    || TABLE_NAME AS TABLE_NAME
  FROM ORACLE_CONNET_BY_TEST
    START WITH parent_Id              = 0
    CONNECT BY NOCYCLE PRIOR TABLE_ID = PARENT_ID
  )
SELECT * FROM T /*--WHERE LVL=1*/