How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

自动清理oracle数据库统一审计记录方案。

查询表空间使用率

SQL> Col tablespace_name for a30
Col used_pct for a10
Set line 120 pages 120
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct 
from (
select tablespace_name, sum(bytes) /1024/1024 as MB 
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_data_files group by tablespace_name) total     
where free.tablespace_name = total.tablespace_name 
order by 4
/SQL> SQL> SQL>   2    3    4    5    6    7    8    9  

TABLESPACE_NAME                  TOTAL_MB    USED_MB USED_PCT
------------------------------ ---------- ---------- ----------
UNDOTBS1                             4215      30.25 .72%
BICD                                 8192    1933.25 23.6%
SYSTEM                               4096    1021.63 24.94%
LOGMINER_TBS                           25          1 4%
OGG_DATA                               20          9 45%
BAKBICD                             13744   10615.44 77.24%
USERS                                4990    4751.38 95.22%
SYSAUX                           32767.98    32708.3 99.82%

8 rows selected.

SQL> col SEGMENT_NAME for a40 
col TABLESPACE_NAME for a12
set line 200 pages 23
select * from (
  select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in 
('SYSTEM','SYSAUX') group by segment_name,tablespace_name order by 2 desc)
  where rownum <=20;
SQL> SQL> SQL>   2    3    4  
SEGMENT_NAME                               TOTAL_MB TABLESPACE_N
---------------------------------------- ---------- ------------
AUD$UNIFIED                                   31284 SYSAUX
IDL_UB1$                                        392 SYSTEM
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                  128 SYSAUX
WRI$_OPTSTAT_HISTGRM_HISTORY                91.0625 SYSAUX
SYS_LOB0000008706C00008$$                   72.1875 SYSAUX
SYS_LOB0000064076C00006$$                   72.1875 SYSAUX
SYS_LOB0000072921C00006$$                    72.125 SYSAUX
I_WRI$_OPTSTAT_H_ST                              72 SYSAUX
C_TOID_VERSION#                                  48 SYSTEM
SYS_LOB0000007422C00004$$                   40.1875 SYSAUX
SOURCE$                                          38 SYSTEM
SYS_LOB0000011147C00038$$                   32.1875 SYSAUX
C_OBJ#_INTCOL#                                   31 SYSTEM
IDL_UB2$                                         30 SYSTEM
SYS_LOB0000076291C00111$$                        29 SYSTEM
WRH$_SYSMETRIC_HISTORY_INDEX                26.4375 SYSAUX
WRH$_SYSSTAT_PK                             24.4375 SYSAUX
WRH$_EVENT_HISTOGRAM_PK                      24.375 SYSAUX
WRH$_SYSMETRIC_HISTORY                      24.3125 SYSAUX
SYS_LOB0000066421C00004$$                   24.1875 SYSAUX

20 rows selected.

SQL> col item for a25
col schema for a20
set line 200 pages 200
SELECT occupant_name "Item", 
         space_usage_kbytes / 1048576 "Space Used (GB)", 
         schema_name "Schema", 
         move_procedure "Move Procedure" 
    FROM v$sysaux_occupants 
  ORDER BY 2 desc;SQL> SQL> SQL>   2    3    4    5    6  

Item                      Space Used (GB) Schema               Move Procedure
------------------------- --------------- -------------------- ----------------------------------------------------------------
AUDSYS                         30.5524292 AUDSYS               DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
SM/AWR                         .413879395 SYS
SM/OPTSTAT                     .338745117 SYS
SDO                            .196411133 MDSYS                MDSYS.MOVE_SDO
SM/ADVISOR                     .136047363 SYS
XDB                            .066955566 XDB                  XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
SM/OTHER                       .049743652 SYS

设置定时任务自动清理

参考：How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'CLEANUP_AUDIT_TRAIL_UNIFIED',
use_last_arch_timestamp => TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC(SYSTIMESTAMP)-30);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Automatically set audit last archive time.');
END;
/