Oracle审计表清理脚本

1.禁用审计

1.1.查看数据库有哪些审计

select * from dba_audit_policies;

1.2.生成禁用审计脚本

select 'exec sys.dbms_fga.enable_policy(object_schema=>'''||object_schema||''',object_name=>'''||object_name||''',policy_name=>'''||policy_name||''',enable=>false);' from dba_audit_policies;

1.3.执行生成的脚本

2.导出

vi exp_fga_log.par

# 不带时间条件
file=fga_log_01.dmp,fga_log_02.dmp,fga_log_03.dmp,fga_log_04.dmp,fga_log_05.dmp
tables=sys.fga_log$ 
filesize=10g 
buffer=99999999
# 带时间条件
query=sys.fga_log$:"where FROM_TZ(ntimestamp#, '00:00') AT LOCAL BETWEEN TO_TIMESTAMP(TO_CHAR(SYSDATE - 123,'YYYYMMDD') || '073000000','YYYYMMDDHH24MISSFF') AND TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYYMMDD') || '073000000','YYYYMMDDHH24MISSFF')"

exp " '/ as sysdba' " parfile=exp_fga_log.par

# 如果导出报错EXP-00091:Exporting questionable statistics
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

3.清空表

-- 需要用sys用户
truncate table sys.fga_log$ drop storage;

4.更换表空间

exec sys.dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>dbms_audit_mgmt.audit_trail_fga_std,audit_trail_location_value=>'new tablespace');

5.重新开启审计

select 'exec sys.dbms_fga.enable_policy(object_schema=>'''||object_schema||''',object_name=>'''||object_name||''',policy_name=>'''||policy_name||''',enable=>true);' from dba_audit_policies;