Archive and Clean Unified Audit Trail

/*
Name: Archive_unified_audit_trail
Desc: A PL/SQL procedure to cleanup unified audit trail with an option to archive        data to an external table
Parameters:
     p_keep_days: Data Retention policy, specified in number of days, 
          for AUD$UNIFIED table. Data for the current day (today) is always
          retained in AUD$UNIFIED
          0  - All audit records older than today will be cleaned up
         >0 - All audit records older than days specefied will be cleaned up
     p_archive_flag: A flag to indicate whether audit records should be archived to
          an external table before doing clean-up
          Y - Archive audit records
     p_archive_dir: An Oracle directory object to specify the location 
          where external table should be created
Examples:
    Cleanup audit trail records older than 10 days. 
    Archive audit records to a location specified by dbadpdir.

    SQL> exec archive_unified_audit_trail (10,'Y','dbadpdir') ;
*/

CREATE OR REPLACE procedure archive_unified_audit_trail 
                            (p_keep_days in number, 
                             p_archive_flag in varchar2, 
                             p_archive_dir  IN VARCHAR2)
is
   lv_sql VARCHAR2(1024);
   lv_sql_2 VARCHAR2(1024) ;
   lv_ext_table_name VARCHAR2(64);
   lv_min_tstamp  TIMESTAMP; 
   lv_max_tstamp  TIMESTAMP; 
   lv_count NUMBER;
   lv_count_ext NUMBER ;

begin
           
       select min(event_timestamp), max(event_timestamp), count(*) 
       into lv_min_tstamp, lv_max_tstamp, lv_count 
       from audsys.aud$unified
       where event_timestamp < trunc(sys_extract_utc(systimestamp)) - p_keep_days ;
       
       dbms_output.put_line('Record Count: ' || lv_count);
       
       if (lv_count > 0) and ( p_keep_days >= 0 )
       then      
            -- archive data if flag is set to Y
            if upper(p_archive_flag) = 'Y'
            then 
                 lv_ext_table_name := 'aud$unified_' ||                     
                                    to_char(lv_min_tstamp,'MMDDRRHH24MISS') || '_' 
                                    || to_char(lv_max_tstamp,'MMDDRRHH24MISS') ; 
       
                 lv_sql :='create table '|| lv_ext_table_name|| chr(10) ||
                          'organization external (type oracle_datapump default 
                                 directory '|| p_archive_dir || chr(10) ||
                          'access parameters (nologfile) '|| ' location('''|| 
                           lv_ext_table_name|| '.dmp''))' || chr(10) || 
                          'as select * from audsys.aud$unified ' || chr(10) ||
                          'where event_timestamp < 
                           trunc(sys_extract_utc(systimestamp)) - ' ||p_keep_days ;
            
                 dbms_output.put_line('External Table Script:');
                 dbms_output.put_line(lv_sql);
                 
                 execute immediate lv_sql ;
                 
                 lv_sql_2 := 'select count(*) from ' || lv_ext_table_name ;                  
                 execute immediate lv_sql_2 into lv_count_ext ; 
                 dbms_output.put_line('Record Count in External Table: ' 
                                        ||lv_count_ext);                                                 
                                  
            end if;           

            -- modified before this time. Doesn't include time specified here.
            -- time in UTC
            
            if ( lv_count = lv_count_ext )
            then
                 dbms_audit_mgmt.set_last_archive_timestamp(
                     audit_trail_type    => dbms_audit_mgmt.audit_trail_unified,
                     last_archive_time   => trunc(sys_extract_utc(systimestamp)) -
                                            p_keep_days);

                 dbms_audit_mgmt.clean_audit_trail(
                      audit_trail_type   => dbms_audit_mgmt.audit_trail_unified,
                     use_last_arch_timestamp  =>  true );
            else
                dbms_output.put_line('Record count in exteranl table do NOT match.
                                      AUD$UNIIFED not purged.');
            end if;

       else
           dbms_output.put_line('Nothing to archive/purge or 
                        parameter p_keep_days is not >=0');
       end if;
end;
/