个人总结

在10gR2以后如果不用DBMS_AUDIT_MGMT这个包管理审计信息的话.全语句直接操作SYS.AUD$表就行. 按照自己参考的工作文档,记得将SYS.AUD$表移出system表空间就行.

How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$ [ID 73408.1]
		Modified 11-SEP-2010     Type BULLETIN     Status PUBLISHED

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.1 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
Checked for relevance on 11-Sep-2010

Purpose

This document explains how to purge, truncate, or delete rows from the audit trail table SYS.AUD$. Starting with Oracle 10gR2 it is possible to use package DBMS_AUDIT_MGMT for this. More information about this package can be read in Note 731908.1 -New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information.

Scope and Application

This document is intended for DBA's or Oracle Support Analysts.

How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$

 
a) To delete rows from the database audit trail table, an appropriate privilege
   is required. You must either be the user SYS, or a user with DELETE ANY TABLE
   system privilege, or a user to whom SYS has granted the object privilege 
   DELETE on SYS.AUD$.

b) To purge audit records from the audit trail, delete all rows:

   -- DELETE FROM sys.aud$;

   To delete rows from the audit trail related to a particular audited table:

   -- DELETE FROM sys.aud$ WHERE obj$name='';

c) If audit trail information must be archived, copy the relevant rows to another 
   table before deletion, using either:

   -- CREATE TABLE  
      AS SELECT * from sys.aud$ WHERE 1=2;

   -- INSERT INTO  
      SELECT  FROM sys.aud$

   -- EXPort the  to an OS file, but do not export SYS.AUD$
      directly.

d) Reducing the size of the audit trail:

   1. If you want to save information currently in the audit trail, copy it to 
      another table and optionally export that table.

   2. SQL> connect / as sysdba
      SQL> TRUNCATE TABLE sys.aud$;
   
      Truncate uses the DROP STORAGE clause which keeps 'minextents' extents,
      thus only 1 extent.

   3. Reload archived audit trail records generated from Step 1.

   The rows inserted require as many extents as necessary to contain current 
   audit trail rows, but no more.

   CAUTION: SYS.AUD$ is the only SYS object that should ever be directly modified

**** The DELETE ANY TABLE privilege only applies to SYS objects if O7_DICTIONARY_ACCESSIBILITY=TRUE