Oracle11g system表空間 AUD$維護

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 26 11:21:18 2012
 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, Data Mining and Real Application Testing options
  
  
 SQL>  select　　
   2　  b.tablespace_name "表空間",
   3　　b.bytes/1024/1024 "大小M",
   4　　(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",
   5　　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "使用率"
   6　　from dba_free_space a,dba_data_files b
   7　　where a.file_id=b.file_id
   8    and b.tablespace_name='SYSTEM'
   9　　group by b.tablespace_name,b.file_name,b.bytes
   10　　order by b.tablespace_name;
  
 表空間                 大小M       已使用M   使用率
 ------------------- ---------- ----------- ----------
 SYSTEM                    4096   3485.9375    85.10 
2.從dba_segments中找出占用SYSTEM表空間中排名前10位的大對象。
SQL> col segment_name for a15;
 SQL> SELECT *
   2    FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
   3            FROM DBA_SEGMENTS
   4           WHERE TABLESPACE_NAME = 'SYSTEM'
   5           GROUP BY SEGMENT_NAME
   6           ORDER BY 2 DESC)
   7   WHERE ROWNUM < 10;
  
 查出aud$占用的很大的空間。 
3.準備truncate aud$表
SQL> show parameter AUDIT_TRAIL
  
 NAME            TYPE        VALUE    
 --------------- ----------- ---------
 audit_trail     string      DB
  
 -- truncate aud$ 表需要有相關的權限。
 SQL> truncate table aud$;
 Table truncated
 4.truncate後檢查system表空間使用情況，發現使用率由85%降低到29.44%SQL> ANALYZE TABLE aud$  COMPUTE STATISTICS;
 Table analyzed. 
  
 SQL>  select　　
  2 　  b.tablespace_name "表空間",
  3  　　b.bytes/1024/1024 "大小M",
  4  　　(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",
  5  　　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "使用率"
  6  　　from dba_free_space a,dba_data_files b
  7  　　where a.file_id=b.file_id
  8     and b.tablespace_name='SYSTEM'
  9  　　group by b.tablespace_name,b.file_name,b.bytes
 10 　　order by b.tablespace_name;
  
 表空間                大小M    已使用M   使用率
 ---------------- ---------- ----------   ----------
 SYSTEM                 4096       1206   29.44 5.為了避免system表空間爆滿對資料的影響，把aud$從system表空間遷移到普通表空間AUD_FILE。
SQL>  BEGIN
   2   DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   3   AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
   4   AUDIT_TRAIL_LOCATION_VALUE => 'AUD_FILE');
   5   END;
   6  /
  
 SQL> col owner for a5;
 SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
   2    FROM DBA_TABLES
   3   WHERE TABLE_NAME = 'AUD$'
   4     AND OWNER = 'SYS';
  
 OWNER TABLE_NAME    TABLESPACE_NAME
 ----- ------------------------------ ------------------------------
 SYS   AUD$                           TD_FILB 
 ---------------------