Overview
select d.referenced_name table_name, count(*) times, sum(count(*))over() tables
from user_dependencies d
where d.referenced_type = 'TABLE'
start with d.name = :name
connect by d.name = prior d.referenced_name
and d.type = prior d.referenced_type
group by d.referenced_name
order by d.referenced_name
;
Hierarchy
select level,d.referenced_owner,d.referenced_name table_name, (select num_rows from dba_tables t where t.owner = d.referenced_owner and t.table_name = d.referenced_name) num_rows
from dba_dependencies d
where d.referenced_type = 'TABLE'
start with d.owner = :owner
and d.name = :name
connect by d.owner = prior d.referenced_owner
and d.name = prior d.referenced_name
and d.type = prior d.referenced_type
;
Not only restricted to views also eg. procedures have dependencies. Just give the root object name as a parameter to the query and you get the dependent tables out.
