1.手工清理:
a.清理审计数据前可先备份一份数据出来:
exp "'/ as sysdba'" tables=SYS.AUD$ file=/path/audit_bak_20160403.dmp log=/path/audit_bak_20160403_exp.log direct=yb.查看当前审计数据大小:select segment_name, bytes/1024/1024/1024 as SIZE_GB from dba_segments where segment_name = 'AUD$';
c.查看审计表总行数:select count(*) from sys.aud$;
d.查看最早一条审计记录的时间:select min(ntimestamp#) from aud$;
e.删除审计数据(可根据需要保留一定时间段的数据):
delete sys.aud$ where NTIMESTAMP# < trunc(sysdate, 'HH') - 90;(删除90天前的数据)
commit;
因delete操作会消耗较多资源,建议根据最早一条审计记录的时间,分批删除审计数据,注意删除后需执行commit命令。
f.审计数据删除后空间并未释放:select segment_name, bytes/1024/1024/1024 as SIZE_GB from dba_segments where segment_name = 'AUD$';
手工释放空间:
alter table sys.aud$ enable row movement;
alter table sys.aud$ shrink space cascade; (compact)
alter table sys.aud$ disable row movement;
再次查询:select segment_name, bytes/1024/1024/1024 as SIZE_GB from dba_segments where segment_name = 'AUD$';
2.自动清理
a.创建清理sys.aud$表的存储过程procedure:
create or replace procedure clear_aud as
begin
delete sys.aud$ where NTIMESTAMP# < trunc(sysdate, 'HH') - 90;
commit;
exception when others then rollback;
end;
/
b.创建清理sys.aud$表的Job:
variable aud_job number;
begin
dbms_job.submit(:aud_job,'clear_aud;',sysdate,'trunc(LAST_DAY(sysdate) + 1)');
commit;
end;
/
当前立即执行,下一次执行在每个月第一天的午夜12点。
c.运行Job
begin
dbms_job.run(:aud_job);
end;
/
d.如果想删除Job,可以使用如下命令:
begin
dbms_job.remove(:aud_job);
end;
/
可通过查看dba_jobs和dba_jobs_running这两个表查看Job运行状态,例如:
select job,last_date,next_date,total_time,broken,interval,failures from dba_jobs;
3.如果执行job无法清除只能通过shell放到后台进行清理
[oracle]# more aud_del.sh
#!/bin/bash
sqlplus '/as sysdba' <<EOF
delete sys.aud$ where ntimestamp# < trunc(sysdate, 'HH') - $1;
commit;
exit;
EOF
oracle]# more test.sh
#!/bin/bash
for (( c=410; c>=90; c-- ))
do
echo "Welcome $c times"
su - oracle -s /bin/bash aud_del.sh $c
done
[oracle]# nohup /home/oracle/test.sh > /home/oracle/output.txt &