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?

Halfbaked

Drop them, and see if you get any calls from your users? :-) – Vermiculation

Define "usage", because there can be a package/proc/function that is seldom used -- doesn't mean it should be removed. – Unbuckle

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

Brit

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!

Gamba

The answer on Ask Tom boils down to creating an audit table and modifying the PL/SQL code to insert on that audit table. That advice only works when the developer is willing or able to made code changes. – Missing

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

Neysa

PL/Scope will not help the OP's situation - they want to know how often a function or procedure is actually used, not potentially used. – Intensive

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.

Aeneas

I don't see how this answers the question, a procedure may have no dependencies, but being called directly from the next layer, and another could have some dependencies, but it isn't called at all nor the other functions that might call it, so never "used". You may want to elaborate on your answer to show how it address the question. I'm not downvoting this because you're new to the site, BTW, welcome to SO! – Frisbie

The original question did not mention whether or not they were looking for callers in another layer. It would be impossible for the Oracle instance itself to know what layers outside the instance were calling a specified function. My response was simply to show dependencies in code that is stored in the Oracle instance. – Aeneas

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.

Suhail

Answer 6

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

Devilry