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

Answer 4

You can see if an object has any dependencies by querying the DBA_DEPENDENCIES table.

SELECT OWNER, 
       NAME, 
       TYPE 
  FROM SYS.DBA_DEPENDENCIES 
 WHERE REFERENCED_OWNER = '<your object owner>' 
   AND REFERENCED_NAME = '<your object name>'
   AND REFERENCED_TYPE IN ('PACKAGE', 'PROCEDURE', 'FUNCTION');

This query will return any dependencies in code stored inside the Oracle instance itself.

It will not reveal whether or not any object is called outside of the instance.

Answer 5

You can use Editors like Toad. They will directly list both the objects on which your procedure is dependent and objects which reference your procedure.

Answer 6

You might also find the pl/sql instrumentation package ILO useful for what you're trying to do.