Oracle – Unified Auditing Trail – AUDSYS

The AUDIT_TRAIL needs to be purged.

Error may occur if USE_LAST_ARCH_TIMESTAMP => TRUE is used:
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 4425
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 825
ORA-06512: at line 2 
(Haven't investigated further) - maybe archive first is required or the archive timestamp has to be set.

Purge example:

SYS: ORCL> select segment_name||'   '||bytes/1024/1024 from dba_segments where owner = 'AUDSYS';

SEGMENT_NAME||''||BYTES/1024/1024
--------------------------------------------------------------------------------
SYS_LOB0000076986C00014$$   62.125
SYS_IL0000076986C00014$$   .0625
CLI_SCN$a48dc988$1$1   2
CLI_TIME$a48dc988$1$1   2
CLI_LOB$a48dc988$1$1   2
CLI_SWP$a48dc988$1$1   88

6 rows selected.

Elapsed: 00:00:00.09
SYS: ORCL> BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   USE_LAST_ARCH_TIMESTAMP => FALSE);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.55
SYS: LASD1> select segment_name||'   '||bytes/1024/1024 from dba_segments where owner = 'AUDSYS';

SEGMENT_NAME||''||BYTES/1024/1024
--------------------------------------------------------------------------------
CLI_SWP$a48dc988$1$1   .0625
CLI_LOB$a48dc988$1$1   .0625
CLI_TIME$a48dc988$1$1   .0625
CLI_SCN$a48dc988$1$1   .0625
SYS_IL0000076986C00014$$   .0625
SYS_LOB0000076986C00014$$   .125

6 rows selected.

Elapsed: 00:00:00.10

Leave a Reply

Your email address will not be published. Required fields are marked *