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

Question

How can you tell if a PL/SQL Package, Procedure, or Function is being used? Is there an Oracle table or view that contains statistics on PL/SQL Package, Procedure, or Function usage?

Answer 1

You can also try querying USER/ALL_source:

SELECT * FROM all_source
where UPPER(TEXT) like UPPER('%procedure_name%')

or

SELECT * FROM all_source
where UPPER(TEXT) like UPPER('%package.function_name%')

You'll have to ignore self references, but that should be easy to spot.

You'll also need to check "view" source from user/all_views. See the other question about querying view source though.

you can also check if a package or top level function/procedure is used with

select * from all_dependencies
where referenced_name like '%PACKAGE_NAME%';

NB: switch user_ with all_/dba_ as needed

if you are specifically looking for uncalled functions then another option is to compiler your code with WARNINGS turned on and then look for PLW-06002 and LPW-06006

exec DBMS_WARNING.add_warning_setting_cat('ALL','ENABLE','SESSION')
create or replace function x return number
as
procedure y is begin null; end;
begin
return 0;
return 1;
end;

show errors

Errors for FUNCTION X:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit X omitted optional AUTHID clause; default value DEFINER used
3/1      PLW-06006: uncalled procedure "Y" is removed.
6/1      PLW-06002: Unreachable code