How To Determine If A Package/Procedure/Function Currently Is In Use

Subject: How To Determine If A Package/Procedure/Function Currently Is In Use

goal: How to determine if a package or stored procedure/function currently is in use
goal: How to identify the user who is currently executing a specific
package or stored procedure/function
fact: Oracle Server - Enterprise Edition
fact: Oracle Server - Standard Edition

fix:
Prior to recompiling a package or stored procedure/function, or to modifying
underlying objects, it can be useful to find out if the object is currently
being executed.

The following query is an example of how to obtain this information. Run the
query in SQL*Plus connected as a user with DBA privileges.

&OBJECT_NAME is to be replaced by the name of the package or stored
procedure/function.

-------------------------------------------

COLUMN TO_NAME FORMAT A14 heading "Object name"
COLUMN USERNAME FORMAT A14 heading "User running"
COLUMN TO_OWNER FORMAT A14 heading "Object owner"

SELECT DISTINCT
o.to_name,
v.username,
o.to_owner,
s.users_executing "Number"
FROM v$object_dependency o,
v$sql s,
v$session v
WHERE o.to_name='&OBJECT_NAME'
AND o.from_address=s.address
AND o.from_hash=s.hash_value
AND o.from_address=v.sql_address
AND o.from_hash=v.sql_hash_value;