Oracle 11g 处理AUD$空间过大问题

查看SYSTEM表空间占用空间排名前10的段是否有AUD$以及FGA_LOG$

SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = ‘SYSTEM’
ORDER BY BYTES DESC)
WHERE ROWNUM < 10;

aud$表及FGA_LOG$数据量

select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in (‘AUD$’,‘FGA_LOG$’);

查询表，truncate该表释放

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

aud$表及FGA_LOG$移动到新tablespace，&AUD_TBS_NAME表示新的表空间名

迁移AUD$表

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘&AUD_TBS_NAME’);
END;
/

迁移FGA_LOG$表

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => ‘&AUD_TBS_NAME’);
END;
/

查询AUD$及FGA_LOG$所在的表空间

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN (‘AUD$’, ‘FGA_LOG$’) ORDER BY table_name;

创建定时任务，定期清理AUD$表

--sys用户procedure 
create or replace procedure
sp_trunc_audit_log is
begin
execute immediate
‘truncate table aud$’;
end;
11授权 
grant execute on sp_trunc_audit_log to system;

11system用户procedure 
create or replace procedure
sp_job_trunc_audit_log is
begin
sys.sp_trunc_audit_log;
end;

--自动调度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;

欢迎联系我一起讨论。我的微信号：Eric_xu_2023

也欢迎关注我的公众号：