[26ai] Cleanup of Obsolete PL/SQL Objects

If you prefer to read in Spanish Spanish version.

Today, let's create an audit in order to clean up unused package/procedure/function in the database, with the porpuse of reducing the total amount of these.

The sintaxis is the following:

Create audit policy "name" Actions execute On "owner"."name";

Alter audit policy "name" Add Actions execute on "owner"."name";

The first sentence defines the audit, and the second sentence adds more objects.

Below, you can see the SQL ad hoc for our audit:

SQL>
Select 
  ( Case When Rownum = 1 Then 'Create' Else 'Alter ' End )   
    || ' AUDIT POLICY CHECK_EXEC ' || 
  ( Case When Rownum = 1 Then ' ' Else ' ADD ' End ) 
    || ' Actions EXECUTE on '||OWNER||'.'||OBJECT_NAME||';' SDDL
  From Dba_Objects 
  where Owner = '&OWNER' 
    and Object_type  in ('PÀCKAGE','FUNCTION','PROCEDURE');

SDDL
---------------------------------------------------------------
Create AUDIT POLICY CHECK_EXEC   Actions EXECUTE on "_owner_"."_name_";
Alter  AUDIT POLICY CHECK_EXEC  ADD  Actions EXECUTE on "_owner_"."_name_";
Alter  AUDIT POLICY CHECK_EXEC  ADD  Actions EXECUTE on "_owner_"."_name_";
...

Lastly , let's activate our audit:

SQL> audit policy CHECK_EXEC;

Once the audit is working, we can check it with the following SQL:

SQL> 
select *
from unified_audit_trail
where action_name = 'EXECUTE'
and object_schema = upper('&OWNER')
and object_name = upper('&OBJECT')
and return_code = 0
order by EVENT_TIMESTAMP desc
fetch first 10 rows only;

After several weeks, we can analyze the information in order to see which objets is unused. The SQL is as follows:

SQL>
With get_Object_bd As (
  Select /*+ MATERIALIZE */
        Owner, Object_Name, object_type
    From dba_Objects 
   Where Owner = '&OWNER' 
     and object_type  in ('PÀCKAGE','FUNCTION','PROCEDURE')
  ),  Audit_Objects as ( 
    Select  /*+ MATERIALIZE */
          object_schema, 
          object_name,
          min(event_timestamp) as first_exec_time,
          max(event_timestamp) as last_exec_time
    from  unified_audit_trail
    where action_name    = 'EXECUTE'
      and object_schema  = '&OWNER'
    group by object_schema, object_name      
)
Select *
From get_Object_bd a 
   left outer join Audit_Objects  b 
  on a.Owner = b.object_schema 
 and  a.object_name = b.object_name;

Also, we can do the same using ASH/AWR instead of auditing, by changing the retention period .

The SQL is as follows:

SQL> 
With get_Object_bd As (
  Select /*+ MATERIALIZE */
         object_id, object_name, object_type
    From dba_Objects 
   Where Owner = '&OWNER' 
     And object_type  in ('PÀCKAGE','FUNCTION','PROCEDURE')
)
select object_id, 
       object_name, 
       object_type,
       min(sample_time) first_seen, max(sample_time) last_seen
from get_Object_bd 
 left outer join dba_hist_active_sess_history 
    on object_id = plsql_entry_object_id
  group by object_id, object_name, object_type;

Looking forward to seeing you in the next article :)