現象：

今天上午發現外網資料庫有幾個sql特別的慢，經過分析這幾個sql是往aud$基表中插資料的sql，這個表現在有記錄數5千多萬，佔據了系統表空間約10g的空間，這樣會導致資料庫的效能下降（雖然oracle11g文件說效能影響可以忽略，但是通過我們的觀察，確實帶來效能下降明細），這樣下去，隨著審計記錄的增長，會有將系統表空間撐爆的可能性。

另外檢視了其他幾個11g的資料庫，都有這個現象，只是其他庫的這張基表的資料目前還沒有那麼大（幾十萬行記錄）。

 

原因分析：

在oracle11g中，資料庫的審計功能是預設開啟的（這和oracle10g的不一樣，10g預設是關閉的），oracle11gR2的官方文件上寫的是錯的，當上說default是none，而且是審計到DB級別的，這樣就會往aud$表裡記錄統計資訊。

 

問題處理方法：

1.如果審計不是必須的，可以關掉審計功能；

SQL> alter system set audit_trail=none scope=spfile;

SQL>showdown immediate;

SQL>startup

2.刪除已有的審計資訊

可以直接truncate表aud$,

或者採取dbms_audit_mgmt來清除。

3.或者將aud$表移到另外一個表空間下，以減少system表空間的壓力和被撐爆的風險。

 

附：11g中有關audit_trail引數的設定說明：

AUDIT_TRAIL

AUDIT_TRAIL enables or disables database auditing.

Values:

• none

  Disables standard auditing. This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default is db.

• os

  Directs all audit records to an operating system file. Oracle recommends that you use the os setting, particularly if you are using an ultra-secure database configuration.

• db

  Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.

  If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

• db, extended

  Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.

  If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

• xml

  Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

• xml, extended

  Performs all actions of AUDIT_TRAIL=xml, and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. These columns are populated only when this parameter is specified.

You can use the SQL AUDIT statement to set auditing options regardless of the setting of this parameter.