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

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

3.清空表

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;