Oracle审计表AUD$处理方法
Oracle审计表AUD$处理方法
Oracle版本:11.2.0,其他版本要测试DBMS_AUDIT_MGMT能否成功
1. 查询表,然后truncate
select count(*) from aud$; truncate table aud$; select count(*) from aud$;
2.创建表空间
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$表移动到新tablespace
BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, audit_trail_location_value => 'ADTTBS'); END; /
4.sys用户procedure:
create or replace procedure sp_trunc_audit_log is begin execute immediate 'truncate table aud$'; end; 授权: grant execute on sp_trunc_audit_log to system;
5.system用户procedure:
create or replace procedure sp_job_trunc_audit_log is begin sys.sp_trunc_audit_log; end;
6.自动调度job
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;