2

I Need to get the list of all the tables that is used in a particular stored procedure in oracle. how to get that list using query?

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
suresh
  • 62
  • 1
  • 7
  • 1
    Try this [answer](http://stackoverflow.com/a/122817/2970947). – Elliott Frisch Jan 20 '14 at 14:26
  • I don't know the specifics of Oracle, but there should be some way to retrieve the execution plan for the SP without actually executing it. That would tell you what you wanted to know (and a good bit more). – Brian A. Henning Jan 20 '14 at 14:27
  • @Lotharyx stored procedures themselves do not have execution plans, although the queries that they contain may. – David Aldridge Jan 20 '14 at 16:54

2 Answers2

3

Dynamic SQLs are skipped, since they're not validated in SEMANTIC checks during compilations

select 
   proc_syn.referenced_owner, 
   proc_syn.referenced_name, 
   proc_syn.referenced_type,
   syn_tab.table_name
from 
   dba_dependencies proc_syn, dba_synonyms syn_tab, dba_tables tables
where 
     proc_syn.name= 'YOUR_PROC' 
  AND REFERENCED_TYPE in ( 'SYNONYM','TABLE')
  AND proc_syn.referenced_name = syn_tab.synonym_name
  AND syn_tab.synonym_name = tables.table_name
  AND syn_tab.owner = 'PUBLIC'
order by 
  proc_syn.referenced_owner, syn_tab.table_name;
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
0
select 
   referenced_owner, 
   referenced_name, 
   referenced_type
from 
   dba_dependencies
where 
   name= 'PROC_NAME' AND REFERENCED_TYPE = 'TABLE'
order by 
   referenced_owner, referenced_name, referenced_type;
hkutluay
  • 6,794
  • 2
  • 33
  • 53