|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|
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$
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-705352/，如需转载，请注明出处，否则将追究法律责任。