How do you find where a stored procedure is used (in other stored procedures)

Question

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

Answer 1

DBA_DEPENDENCIES view has all the answers to such questions.

select * from DBA_DEPENDENCIES
  where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';

Answer 2

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
where UPPER(TEXT) like UPPER('%STORED_PROCEDURE_NAME%')

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

Answer 3

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%')) 
;