In fact, with only a little addition, you can calculate all convex
hulls within your database, for every "origin" table:
-------------------------------------------------
-- "graph" denotes an undirected foreign key reference graph
-- for schema "TEST"
with graph as (
select c1.table_name t1, c2.table_name t2
from all_constraints c1
join all_constraints c2
on c1.owner = c2.r_owner
and c1.constraint_name = c2.r_constraint_name
where c1.owner = 'TEST'
union all
select c2.table_name t1, c1.table_name t2
from all_constraints c1
join all_constraints c2
on c1.owner = c2.r_owner
and c1.constraint_name = c2.r_constraint_name
where c1.owner = 'TEST'
),
-- "paths" are all directed paths within that schema as a #-delimited string
paths as (
select sys_connect_by_path(t1, '#') || '#' path
from graph
connect by nocycle prior t1 = t2
),
-- "subgraph" are all those directed paths that go trough a given table T_AUTHOR
subgraph as (
select distinct t.table_name, regexp_replace(p.path, '^#(.*)#$', '\1')
path
from paths p
cross join all_tables t
where t.owner = 'TEST'
and p.path like '%#' || t.table_name || '#%'
),
-- This XML-trick splits paths and generates rows for every distinct
split_paths as (
select distinct table_name origin,
cast(t.column_value.extract('//text()') as varchar2(4000)) table_names
from subgraph,
table(xmlsequence(xmltype(
'<x><x>' || replace(path, '#', '</x><x>') ||
'</x></x>').extract('//x/*'))) t
)
select origin, listagg(table_names, ', ') within group (order by 1)
from split_paths
group by origin
-------------------------------------------------
Within the jOOQ integration test database, the above yields (result CSV-export):
-------------------------------------------------
"ORIGIN","LISTAGG(TABLE_NAMES,',')WITHINGROUP(ORDERBY1)"
"T_658_11","T_658_11, T_658_12, T_658_21, T_658_22, T_658_31,
T_658_32, T_658_REF"
"T_658_12","T_658_11, T_658_12, T_658_21, T_658_22, T_658_31,
T_658_32, T_658_REF"
"T_658_21","T_658_11, T_658_12, T_658_21, T_658_22, T_658_31,
T_658_32, T_658_REF"
"T_658_22","T_658_11, T_658_12, T_658_21, T_658_22, T_658_31,
T_658_32, T_658_REF"
"T_658_31","T_658_11, T_658_12, T_658_21, T_658_22, T_658_31,
T_658_32, T_658_REF"
"T_658_32","T_658_11, T_658_12, T_658_21, T_658_22, T_658_31,
T_658_32, T_658_REF"
"T_658_REF","T_658_11, T_658_12, T_658_21, T_658_22, T_658_31,
T_658_32, T_658_REF"
"T_AUTHOR","T_AUTHOR, T_BOOK, T_BOOK_DETAILS, T_BOOK_SALE,
T_BOOK_STORE, T_BOOK_TO_BOOK_STORE, T_LANGUAGE"
"T_BOOK","T_AUTHOR, T_BOOK, T_BOOK_DETAILS, T_BOOK_SALE, T_BOOK_STORE,
T_BOOK_TO_BOOK_STORE, T_LANGUAGE"
"T_BOOK_DETAILS","T_AUTHOR, T_BOOK, T_BOOK_DETAILS, T_BOOK_SALE,
T_BOOK_STORE, T_BOOK_TO_BOOK_STORE, T_LANGUAGE"
"T_BOOK_STORE","T_AUTHOR, T_BOOK, T_BOOK_DETAILS, T_BOOK_SALE,
T_BOOK_STORE, T_BOOK_TO_BOOK_STORE, T_LANGUAGE"
"T_BOOK_TO_BOOK_STORE","T_AUTHOR, T_BOOK, T_BOOK_DETAILS, T_BOOK_SALE,
T_BOOK_STORE, T_BOOK_TO_BOOK_STORE, T_LANGUAGE"
"T_DIRECTORY","T_DIRECTORY"
"T_LANGUAGE","T_AUTHOR, T_BOOK, T_BOOK_DETAILS, T_BOOK_SALE,
T_BOOK_STORE, T_BOOK_TO_BOOK_STORE, T_LANGUAGE"
"X_TEST_CASE_64_69","X_TEST_CASE_64_69, X_TEST_CASE_71,
X_TEST_CASE_85, X_UNUSED"
"X_TEST_CASE_71","X_TEST_CASE_64_69, X_TEST_CASE_71, X_TEST_CASE_85, X_UNUSED"
"X_TEST_CASE_85","X_TEST_CASE_64_69, X_TEST_CASE_71, X_TEST_CASE_85, X_UNUSED"
"X_UNUSED","X_TEST_CASE_64_69, X_TEST_CASE_71, X_TEST_CASE_85, X_UNUSED"
-------------------------------------------------
