SYSTEM表空间AUD$使用空间过大问题处理
ERROR：SYSTEM表空间使用率超过99%，开发用户连接不上，报错如下图：
ACTION:
1.查看空间的使用情况：
---查看system表空间的大小
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';
TABLESPACE_NAME TOTAL USED FREE % USED
------------------------------ ---------- ---------- ---------- ----------
% FREE
----------
SYSTEM 32.9433594 31.999939 1010892800 97.136235
2.85783631
2.查看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;
3.截断占用空间最大的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$';
BYTES/1024/1024/1024
--------------------
31.2255859
SQL> truncate table sys.AUD$;
Table truncated.
SQL> select bytes/1024/1024/1024 from dba_segments where segment_name='AUD$';
BYTES/1024/1024/1024
--------------------
.000061035
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';
TABLESPACE_NAME TOTAL USED FREE % USED
------------------------------ ---------- ---------- ---------- ----------
% FREE
----------
SYSTEM 32.9433594 .839782715 3.4469E+10 2.54917146
* system空间已回收，可根据审计文件信息是否需要，如若需要部分审计信息可用delete删除
4.更改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;
/
5.若确认不需要审计，直接将审计功能关闭
alter system set audit_trail=none scope=spfile;
需要重启才生效