Thursday, October 31, 2013

How to get list of tables used in a stored procedure or package

Try to run below query and privide the first value as the owner of this procedure and second value as the procedure name

select
   owner,
   type,
   name,
   referenced_owner,
   referenced_type,
   referenced_name
from
   dba_dependencies
where
   ((owner like upper('&1')
   and
      name like upper('&2') )
   or
   (referenced_owner like upper('&1')
   and
   referenced_name like upper('&2') ))
   and
   referenced_owner != 'SYS'
   and
   referenced_type != 'NON-EXISTENT'
order by
   owner, type, name;

This will give you a list of all tables , synonym , other packages used in your stored procedure.

For more information please see the below link
http://www.dba-oracle.com/t_tables_referenced_inside_pl_sql_procedures.htm 

No comments:

Post a Comment