system01.dbf檔案過大——SYSTEM表空間AUD$使用空間過大問題處理

巡檢時發現oracle資料庫的system01.dbf資料檔案超大，有32G大小，下面對system01.dbf進行排查占用大的問題及縮小空間

1、檢查system表空間中占用空間最大對象

SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

system01.dbf檔案過大——SYSTEM表空間AUD$使用空間過大問題處理

aud$為審計功能

或用下面語句檢視更詳細些

檢視system表空間中各個段占用空間的情況：

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' and rownum<20 GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE ORDER BY 4;

2、截斷占用空間最大的AUD$表（需要确認審計資訊是否需要保留）：

---檢視該表的大小：

SQL> select TABLE_NAME,NUM_ROWS,BLOCKS*8192/1024/1024/1024 from dba_tables where table_name='AUD$';

SQL> select bytes/1024/1024/1024 from dba_segments where segment_name='AUD$';

---清除aud$的資料

SQL> truncate table sys.AUD$; 或 truncate table aud$;清除都可
Table truncated.


SQL> select bytes/1024/1024/1024  from dba_segments where segment_name='AUD$';
BYTES/1024/1024/1024
--------------------
.000061035

---檢視system表空間的大小

SQL> SELECT a.tablespace_name, a.bytes/1024/1024/1024 total, b.bytes/1024/1024/1024 used, c.bytes free, (b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name and a.tablespace_name='SYSTEM';

* system空間已回收，可根據審計檔案資訊是否需要，如若需要部分審計資訊可用delete删除

3、更改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;

4、若确認不需要審計，直接将審計功能關閉

alter system set audit_trail=none scope=spfile;

需要重新開機才生效

5、釋放system01.dbf檔案占用空間

select a.file#, a.name, a.bytes/1024/1024 CurrentMB, 
       ceil(HWM * a.block_size)/1024/1024 ResizeTo, 
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB, 
       'alter database datafile '''||a.name||''' resize '|| ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD 
from   v$datafile a, 
       ( select file_id, max(block_id+blocks-1) HWM 
         from   dba_extents 
         group by file_id) b 
where  a.file# = b.file_id(+) and (a.bytes - HWM *block_size)>0 order by 5;
     FILE#
----------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
 CURRENTMB   RESIZETO  RELEASEMB
---------- ---------- ----------
RESIZECMD
------------------------------------------------------------------------------------------------------------------------------------------------------
         1
/u01/app/oracle/oradata/utf8/system01.dbf
     32730 1277.99219 31452.0078
alter database datafile '/u01/app/oracle/oradata/utf8/system01.dbf' resize 1278M;

alter database datafile '/u01/app/oracle/oradata/utf8/system01.dbf' resize 1278M;