审计表AUD$引起system表空间异常增长

问题背景：

客户反馈system表空间总是满，并没有其他业务用户使用system表空间

问题解决：

怀疑是审计占用

SYSTEM表空间使用率达到了85%,查出是用来记录审计记录的aud$表占用了很大的空间。

备份后truncate掉AUD$,问题临时解决。

记得oracle11.2可以把aud$迁移到普通的表空间。于是试了一把，果然ok。

1.检查SYSTEM表空间使用情况

 1 SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 26 11:21:18 2012
 2 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 3 Connected to:
 4 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 5 With the Partitioning, Data Mining and Real Application Testing options
 6 
 7 
 8 SQL> select　　
 9 2　 b.tablespace_name "表空间",
10 3　　b.bytes/1024/1024 "大小M",
11 4　　(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",
12 5　　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率"
13 6　　from dba_free_space a,dba_data_files b
14 7　　where a.file_id=b.file_id
15 8 and b.tablespace_name='SYSTEM'
16 9　　group by b.tablespace_name,b.file_name,b.bytes
17 10　　order by b.tablespace_name;
18 
19 表空间 大小M 已使用M 利用率
20 ------------------- ---------- ----------- ----------
21 SYSTEM 4096 3485.9375 85.10

 

2.从dba_segments中找出占用SYSTEM表空间中排名前10位的大对象。

1 SQL> col segment_name for a15;
2 SQL> SELECT *
3 2 FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
4 3 FROM DBA_SEGMENTS
5 4 WHERE TABLESPACE_NAME = 'SYSTEM'
6 5 GROUP BY SEGMENT_NAME
7 6 ORDER BY 2 DESC)
8 7 WHERE ROWNUM < 10;

查出aud$占用的很大的空间。
3.准备truncate aud$表

1 SQL> show parameter AUDIT_TRAIL
2 
3 NAME TYPE VALUE 
4 --------------- ----------- ---------
5 audit_trail string DB

-- truncate aud$ 表需要有相关的权限。

1 SQL> truncate table aud$;
2 Table truncated

4.truncate后检查system表空间使用情况，发现使用率由85%降低到29.44%

 1 SQL> ANALYZE TABLE aud$ COMPUTE STATISTICS;
 2 Table analyzed. 
 3 
 4 SQL> select　　
 5 2 　 b.tablespace_name "表空间",
 6 3 　　b.bytes/1024/1024 "大小M",
 7 4 　　(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",
 8 5 　　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率"
 9 6 　　from dba_free_space a,dba_data_files b
10 7 　　where a.file_id=b.file_id
11 8 and b.tablespace_name='SYSTEM'
12 9 　　group by b.tablespace_name,b.file_name,b.bytes
13 10 　　order by b.tablespace_name;
14 
15 表空间 大小M 已使用M 利用率
16 ---------------- ---------- ---------- ----------
17 SYSTEM 4096 1206 29.44

 

5.为了避免system表空间爆满对数据的影响，把aud$从system表空间迁移到普通表空间AUD_FILE。

 1 SQL> BEGIN
 2 2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
 3 3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
 4 4 AUDIT_TRAIL_LOCATION_VALUE => 'AUD_FILE');
 5 5 END;
 6 6 /
 7 
 8 SQL> col owner for a5;
 9 SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
10 2 FROM DBA_TABLES
11 3 WHERE TABLE_NAME = 'AUD$'
12 4 AND OWNER = 'SYS';
13 
14 OWNER TABLE_NAME TABLESPACE_NAME
15 ----- ------------------------------ ------------------------------
16 SYS AUD$ TD_FILB
17 
18 ————————————————