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*/
  

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 ='GDS_OWNER'
and o.owner = 'GDS_OWNER'
and o.object_type ='TABLE'
),
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 ='GDS_OWNER'
and o.owner = 'GDS_OWNER'
and o.object_type ='TABLE'
),
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 PRIOR TABLE_ID = PARENT_ID )
SELECT * FROM T --WHERE LVL=1


select * from dba_objects where object_name='CLM'