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

Answer 2

Not by default. But you can use the audit functionality of your Oracle database. At Ask Tom is a long thread about the auditing of procedure calls!

Answer 3

If you're on Oracle 11 (R2?), I'd give PL/Scope a chance.

The docu states: PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage in the source code.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

You can find more about it at http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#g1010526