DBMS_AUDIT_MGMT

In the examples below you will see the use of Constants like DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS and DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD to identify the audit trail type. The full list is:

One-Time Initialisation

Before the audit trail can be cleaned up for the first time, a one-time initialisation process must be executed.

Check

Check if audit trail cleanup is initialized:

SET SERVEROUTPUT ON

BEGIN

  IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS)

    THEN DBMS_OUTPUT.PUT_LINE('YES');

    ELSE DBMS_OUTPUT.PUT_LINE('NO');

  END IF;

END;

/

Initialize

BEGIN

  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

                               default_cleanup_interval  => 24 );

END;

/

Purge

Check Cut-Off Date

To check the setting in effect use...

SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

Set Cut-Off Date

BEGIN

   DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type    => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

                                              last_archive_time   => TO_TIMESTAMP('2009-09-30 10:00:00','YYYY-MM-DD HH24:MI:SS'),

                                              rac_instance_number => NULL);

END;

/

If you just want to keep the last 30 days,, use something like this instead...

BEGIN

   DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE    => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

                                              LAST_ARCHIVE_TIME   => SYSTIMESTAMP-30,

                                              RAC_INSTANCE_NUMBER => NULL );

END;

/

Purge

To purge the audit entries older than the timestamp set in the previous step...

BEGIN

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

                                    use_last_arch_timestamp => TRUE);

END;

/

Batch Size

• The procedure automatically groups the deletes into batches and executes each batch as a single transaction. 

• The Batch size is shown in the DBA_AUDIT_MGMT_CONFIG_PARAMS view.

• The batch size defaults to 10000 rows and valid values can be anywhere between 100 and 1000000.

• The most common reason to change this value is if the transactions are taking too long.

Update using...

EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

                                              DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,

                                              10000);

If the AUDIT trail is not in a suitable tablespace (e.g. if it's in SYSTEM), you can move it using these steps...

BEGIN

  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

                                           audit_trail_location_value => 'AUDIT_DATA') ;

END;

/ 

Bibliography