Oracle Database Administration: The Essential Refe

Example Script to Clean Out the Audit Trail

The following script is used in conjunction with the previous script. In this example, you must be connected to the instance as SYS.

DELETE FROM aud$ a
WHERE logoff$time < trunc(sysdate)
AND action BETWEEN 101 AND 102
AND EXISTS
(SELECT 'x' FROM system.dba_audit_session_daily d
    WHERE trunc(a.timestamp)   = d.timestamp);

In this example, we are using audit actions 101 and 102. These correspond to the ‘LOGOFF’ and ‘LOGOFF BY CLEANUP’ actions used in the previous example. The AUDIT_ACTIONS table provides the mapping between the ACTION_NAME used in DBA_AUDIT_SESSIONS and the ACTION stored in AUD$.