[26ai] Limpieza de objetos obsoletos en desuso

El artículo también se encuentra publicado en Ingles English version.

En el artículo de hoy vamos a definir una auditoría con el objetivo de hacer limpieza de package/procedure/function en una base de datos que ya no se utilizan, con el objetivo de reducir el gran volumen que hay .

La sintaxis para definir la AUDIT será la siguiente:

Create audit policy "_name_" 
 Actions execute On "_owner_"."_name_";

Alter audit policy "_name_" 
  Add Actions execute on "_owner_"."_name_";

La primera sentencia sería la que define nuestra policy, mientras que la segunda sentencia sería para añadir más objetos.

La SQL ad hoc con la sentencia preparada sería la siguiente:

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_";
...

Como último paso , activamos la auditoría:

SQL> audit policy CHECK_EXEC;

Una vez que lo tenemos activa podemos ir vigilando la información que nos va auditando:

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;

Una vez que hemos dejado el tiempo suficiente, procedemos a extraer la información con la siguiente consulta:

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 

Esto mismo que hemos hecho con una audit, lo podemos hacer cambiando la profundidad del periodo de retención de ASH/AWR.

La consulta debería hacerse sobre la DBA_HIST_ACTIVE_SESS_HISTORY.

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

Espero que os guste. ¡Nos vemos en el próximo artículo!