Saturday, October 4, 2008

How to truncate or delete rows from audit trail table sys.aud$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.
SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;
Now export the table as,
SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp

3)To delete all records from audit trail table SYS.AUD$ issue,
SQL>DELETE FROM SYS.AUD$;

To delete all records of particular audited table from the audit trail issue,
SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.

4)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;


Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/10/how-to-reorganize-audit-trail-sysaud.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html

1 comment:

  1. a user cannot delete all the records from sys.aud$, DML on sys.aud$ is audited and these records can only be delete by SYS. See also metalink note 388169.1

    ReplyDelete