2

I am working with Oracle 12c and need to find all references where a specific table or view is being used in Stored Procedure/Function and packages.

I have found a this answer about MS SQL Server, but it's not related to Oracle, besides sp_help and sp_depends sometimes return inaccurate results.

I know to search in column text of table all_source, for example, this code (search only standard user defined package names, not system package):

SELECT   type, name, line, text 
   FROM  all_source 
   WHERE type = 'PACKAGE BODY'
     AND name like 'P%' 
     AND UPPER(text) like '%' || p_table_or_view_name || '%'
   ORDER BY name, line;

but I'm looking if there's a more elegant and/or standard solution in Oracle.

I'm also checking if this answer can help me in any way.

I will appreciate any assistance.

Community
  • 1
  • 1
Binyamin Regev
  • 914
  • 5
  • 19
  • 31

1 Answers1

3

Use the ALL_DEPENDENCIES dictionary table:

SELECT *
FROM   ALL_DEPENDENCIES
WHERE  referenced_name = 'YOUR_TABLE_NAME'
AND    owner           = 'YOUR_USER';
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Also, it would be good if you use admin user login which. Normal user may not have access to list the dependencies. – Maverick Feb 01 '18 at 16:47
  • This solution will not list the stored (sub) programs where the table is referred in dynamic SQL. – ArtBajji Jan 21 '19 at 03:59