Oracle audit table AUD $ Handling Method

Oracle audit table AUD $ processing method Oracle version: 11.2.0, other versions should test whether DBMS_AUDIT_MGMT is successful 1. query the table, and then truncate

     select count(*) from aud$;     truncate table aud$;     select count(*) from aud$;

 

2. Create a tablespace

create tablespace adttbs        datafile '/oracle/OMT/admin/oradata/aud01.dbf'  size 2G autoextend on;SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

 

3. aud $ move table to new tablespace

BEGINDBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,audit_trail_location_value => 'ADTTBS');END;/

 

4. sys user procedure:

Create or replace procedure sp_trunc_audit_log isbegin execute immediate 'truncate table aud $ '; end; authorization: grant execute on sp_trunc_audit_log to system;

 

5. system user procedure:

create or replace procedure sp_job_trunc_audit_log isbegin    sys.sp_trunc_audit_log;end;

 

6. Automatic job Scheduling

BEGIN  DBMS_SCHEDULER.CREATE_JOB   (    job_name   => 'day_trunc_audit_log',    job_type   => 'STORED_PROCEDURE',    job_action => 'SP_JOB_TRUNC_AUDIT_LOG',    start_date => sysdate,    repeat_interval => 'FREQ=DAILY; BYHOUR=04; BYMINUTE=05;INTERVAL=1',    enabled         => true,    comments        => 'every day truncate table audit log'  );END;