Find tables referenced inside PL/SQL stored procedures
Oracle Tips by Burleson Consulting
Question: I have
hundreds of Stored procedures, and I need to know all the tables
and column names used in each PL/SQL stored procedure. In a
stored procedure, there are can many select, Update, insert,
create, truncate statements. I want to know all the
columns used in the query, all the tables from which the data is
How do I query the dictionary to see the tables within by stored
Answer: First, see
notes on using the dba_dependencies view.
The Oracle data dictionary tracks the object types referenced in
PL/SQL with the dba_dependencies view. To track the
dependency among packages and tables, try this dictionary query:
owner = 'SCOTT'
referenced_owner, referenced_name, referenced_type;
Here is a more sophisticated script to show all
tables inside a stored procedure, published by Antony Ananth:
((owner like upper('&1')
name like upper('&2') )
(referenced_owner like upper('&1')
referenced_owner != 'SYS'
owner, type, name;
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.