“The world, even the smallest parts of it, is filled with things you don’t know.”

Sherman Alexie

Someone asked me a ‘simple’ question regarding managing Oracle code base:

how can I find out which plsql stored code is not used in an oracle database?

As there is almost always the case with Oracle database topics, it turns out that a short, clear one liner answer is not available. Rather, I think there are several checks to be done from several contexts.

To identify unused PL/SQL code (like stored procedures, functions, or packages) in an Oracle database, you’ll need to track code usage indirectly, as Oracle doesn’t directly track unused PL/SQL objects.

Here are some strategies to identify potentially unused code:

Audit Code Execution Using Oracle’s Auditing and Triggers

Enable auditing

Enable auditing for PL/SQL execution if it’s not already in place.

You can use Unified Auditing or database triggers to log the use of specific PL/SQL objects.

/* disable and drop any other existing policy with the same name
NOAUDIT POLICY exec_proc_policy;
drop audit policy exec_proc_policy;
*/
-- create the policy
create audit policy exec_proc_policy
    actions execute on radu.add_employee;
-- enable the policy
AUDIT POLICY exec_proc_policy;
-- check the new policy is created
select * from AUDIT_UNIFIED_POLICIES where object_schema = 'RADU';
-- check the audit lines for the test procedure 'ADD_EMPLOYEE'
select count(*) from UNIFIED_AUDIT_TRAIL where OBJECT_NAME = 'ADD_EMPLOYEE';
-- Test with a simple procedure
set SERVEROUTPUT on
execute ADD_EMPLOYEE (1, 'a', 1, 2);
-- verify a new audit line is inserted
select count(*) from UNIFIED_AUDIT_TRAIL where OBJECT_NAME = 'ADD_EMPLOYEE';

Set up Triggers

Set up triggers on key tables to log which procedures are called. For example, if a package MY_PACKAGE is used only when table MY_TABLE is accessed, you can add triggers to log this in an audit table.

   CREATE TABLE usage_log (
       object_name VARCHAR2(50),
       usage_date DATE
   );

   CREATE OR REPLACE TRIGGER log_usage
   AFTER INSERT OR UPDATE OR DELETE ON MY_TABLE
   BEGIN
       INSERT INTO usage_log (object_name, usage_date)
       VALUES ('MY_PACKAGE.PROCEDURE_NAME', SYSDATE);
   END;

This can help you track whether a stored procedure or package is being executed over time.

Use Oracle Database Views to Track Dependencies

• The DBA_DEPENDENCIES view shows dependencies between database objects. You can check whether each PL/SQL procedure or package is referenced by other database objects like triggers, views, or procedures.
• If a stored procedure has no dependencies, it might be a candidate for unused code, but this should be validated further as it could still be used by external applications.

   SELECT *
   FROM DBA_OBJECTS
   WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
     AND OBJECT_NAME NOT IN (
       SELECT REFERENCED_NAME
       FROM DBA_DEPENDENCIES
       WHERE REFERENCED_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
     );

Enable and Review Oracle’s DBMS_PROFILER Usage Data

• DBMS_PROFILER is a tool for profiling PL/SQL code execution. You can activate it to record execution data for each PL/SQL object.
• Start a profiling session, run your application(s), and check the profiler tables afterward to see which procedures or functions were executed. Steps:

1. Run DBMS_PROFILER.START_PROFILER.
2. Execute application code that should cover all normal use cases.
3. Run DBMS_PROFILER.STOP_PROFILER.
4. Review PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, and PLSQL_PROFILER_DATA tables for usage data.

Analyze Application Code or Logs for References

• Analyze application code, such as Java or .NET applications, that connects to the database to see if specific PL/SQL objects are referenced.
• Review server logs or audit logs to see which objects are called. If certain procedures or functions are not logged over a long period, they may be unused.

Review Database Execution Plan and AWR Reports

• Automatic Workload Repository (AWR) Reports: If AWR is enabled, it can give insights into frequently and rarely used SQL and PL/SQL. If a specific object doesn’t appear in AWR reports over time, it may not be used.

Check Last DDL Date

• Objects that haven’t been modified or recompiled in a long time may be inactive. You can use the LAST_DDL_TIME column in DBA_OBJECTS to identify objects that haven’t been modified recently.

   SELECT OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME
   FROM DBA_OBJECTS
   WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
     AND LAST_DDL_TIME < ADD_MONTHS(SYSDATE, -12);

Combining these approaches should give you a reasonable indication of which PL/SQL code is potentially unused, though it’s essential to validate these findings, especially if external applications may be invoking the code indirectly.

Remove the code from from the production database

After a short list of unused code units is built it would be a good idea to not proceed with dropping the code without a period of grace during which the code is kept stored in the database but it is made unavailable directly for use. A couple of ways to achieve this would be:

Invalidate the objects suspected to be unused

If we have a short-list of PLSQL objects that would be unused, one option is to invalidate the objects and monitor: if they are used in the meantime the object will be compiled with a small performance impact. So if we invalidate the object and we find it valid after a while knowing that no one else attempted a compilation, we could draw the conclusion that the object is being used and should not be dropped:

DBMS_UTILITY.INVALIDATE ( 
   p_object_id              NUMBER,
   p_plsql_object_settings  VARCHAR2 DEFAULT NULL,
   p_option_flags           PLS_INTEGER DEFAULT 0);

select object_id from user_objects
where object_name = 'ADD_EMPLOYEE'
;

 OBJECT_ID
----------
     99315

execute DBMS_UTILITY.INVALIDATE (99315);

execute ADD_EMPLOYEE (1, 'a', 1, 2);

Rename the suspected code units

Develop a process to rename the code suspected to be of no use

Retrieve and Copy the Procedure Code:

SELECT TEXT
FROM USER_SOURCE
WHERE NAME = 'ADD_EMPLOYEE'
ORDER BY LINE;

Create the New Procedure:

CREATE OR REPLACE PROCEDURE NEW_PROCEDURE_NAME AS
BEGIN
-- Procedure logic here
END;

Drop the Old Procedure

DROP PROCEDURE OLD_PROCEDURE_NAME;

Revoke all the rights on the code unit (especially execution)

To revoke EXECUTE access from all users except the owner, you may need to check the ALL_TAB_PRIVS view for specific privileges granted and revoke them individually.

revoke EXECUTE on "RADU"."ADD_EMPLOYEE" from "SSB"
;

Continue auditing and the other measures as described above.

If after the grace period is expired (we recommend at least one year so all period-end and year-end workloads are run at least once) it is likely safe to drop the stored objects but not before a backup of the code in taken and archived.

Also the code removal changes should be progressed from lower environments up to production.