2013-04-12

Tables used

Having a view stack? Trying to find out tables a view is using?

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.

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.