Recursively walktrough Oracle's constraints
WITH objectscte
AS
(SELECT ao.owner,
ao.object_name, ao.object_type
FROM all_objects
ao
WHERE ao.owner
= '&owner'
AND NOT ao.object_name
LIKE 'BIN$%'
AND NOT ao.object_name
LIKE 'DMRS_%'
AND NOT ao.object_name
LIKE 'DMRV_%'
AND NOT ao.object_name
LIKE 'DT_%'
AND NOT ao.object_name
LIKE 'CG$%'),
constraintscte AS
(SELECT ac.owner
,ac.table_name
xname
,'TABLE' xtype
,acr.owner
referenced_owner
,acr.table_name
referenced_name
,'TABLE' referenced_type
FROM all_constraints
ac
INNER JOIN all_constraints
acr
ON ac.r_owner
= acr.owner
AND ac.r_constraint_name
= acr.constraint_name
WHERE ac.owner
= '&owner'
AND NOT ac.table_name
LIKE 'BIN$%'
AND NOT ac.table_name
LIKE 'DMRS_%'
AND NOT ac.table_name
LIKE 'DMRV_%'
AND NOT ac.table_name
LIKE 'DT_%'
AND NOT ac.table_name
LIKE 'CG$%'
AND ac.constraint_type
= 'R'),
nonreferencedobjectscte AS
(SELECT *
FROM objectscte
oc
WHERE NOT EXISTS (SELECT 1
FROM constraintscte
cc
WHERE cc.referenced_owner = oc.owner
AND cc.referenced_name
= oc.object_name
AND cc.referenced_type
= oc.object_type)),
orphanedobjectscte AS
(SELECT *
FROM nonreferencedobjectscte
nroc
WHERE NOT EXISTS (SELECT 1
FROM constraintscte
cc
WHERE cc.owner = nroc.owner
AND cc.xname
= nroc.object_name
AND cc.xtype
= nroc.object_type)),
nonorphanedobjectscte AS
(SELECT *
FROM nonreferencedobjectscte
nroc
WHERE EXISTS (SELECT 1
FROM constraintscte
cc
WHERE cc.owner = nroc.owner
AND cc.xname
= nroc.object_name
AND cc.xtype
= nroc.object_type)),
constraintsrecursivecte(owner,
xname,
xtype,
xdepth,
xpath) AS
(SELECT nooc.owner
,nooc.object_name
,nooc.object_type
,1
,to_clob(nooc.owner || '.' || nooc.object_name)
FROM nonorphanedobjectscte
nooc
UNION ALL
SELECT cc.referenced_owner
,cc.referenced_name
,cc.referenced_type
,crc.xdepth
+ 1
,to_clob(to_char(crc.xpath) || '\' || cc.referenced_owner
|| '.' ||
cc.referenced_name)
FROM constraintsrecursivecte
crc
INNER JOIN constraintscte
cc
ON crc.owner
= cc.owner
AND crc.xname
= cc.xname
AND crc.xtype
= cc.xtype) search depth FIRST BY owner,
xname,
xtype SET xorder
cycle
owner,
xname,
xtype SET xcycle
TO 1 DEFAULT 0
SELECT owner, xname,
xtype, xdepth, to_char(xpath), xorder, xcycle
FROM constraintsrecursivecte;