I have a stored procedure which I want to refactor, in a database which has thousands of SPs. Is there a quick way of finding references to that stored procedure in other SPs so I can be sure that I'm not breaking any other code when I refactor.

In the application code I can look for calls to the SP easily enough, and I can do a text search over all the various sql files which define the SPs, but it's possible there may be some SPs in the database which may be missed that way.

EDIT: The stored procedures I'm trying to locate are part of a package.

EDIT: I'm running on Oracle 11g

3 Answers 3


DBA_DEPENDENCIES view has all the answers to such questions.

select * from DBA_DEPENDENCIES
  where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';
  • 2
    This approach will not however work if you use dynamic sql. i.e. if you execute a procedure as part of dynamic sql. Otherwise dba_ or all_dependencies will work great.
    – Raj
    Jan 7, 2015 at 13:33
  • 1
    This is helpful, and I can locate functions and procedures which are defined by a user, but I don't seem to be able to find FNs or SPs which are defined in a package. Any thoughts? Jan 7, 2015 at 14:08
  • In this case one has to search for a package. DBA_DEPENDENCIES show us more like what will be invalidated if particular object is droped. So e.g. you can find what views references table. Jan 7, 2015 at 19:51

This appears to work - it's less elegant than @MindaugasRiauba's answer, but it does seem to find references from all packages.

SELECT * FROM all_source

(From How can you tell if a PL/SQL Package, Procedure, or Function is being used?)


I had a similar situation, only that I needed to retrieve a list of packages that use a specific package; so I made this query, maybe it helps:

with dep2 as (
    select dep.*
    from all_dependencies dep
    where dep.owner not in ('SYS', 'SYSTEM', 'PUBLIC', 'XDB')
    and dep.referenced_owner not in ('SYS', 'SYSTEM', 'PUBLIC', 'XDB')
    and dep.referenced_type = 'PACKAGE'
    and dep.dependency_type != 'NON-EXISTENT'
    and (dep.referenced_owner || '.' || dep.referenced_name) != (dep.owner || '.' || dep.name)
dep3 as (
    select owner || '.' || name as child,
    referenced_owner || '.' || referenced_name as parent
    from dep2
select connect_by_root parent, lpad(' ',2*(level-1)) || to_char(child) 
from dep3
start with parent = 'SCHEMA.PACKAGE_NAME'
connect by nocycle prior child = parent 
and exists (select 1 from all_source where (owner || '.' || name) = dep3.child and upper(text) like upper('%optional, some string you may want to search%')) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.