How to Backup and Truncate Audit table (sys.aud$) in Oracle

December 14, 2018

SYS.AUD$ table which is also referred as audit trial is a table inside the sys schema which is used to store database auditing information.

truncate sys.aud$ table in Oracle

Depending on the configurations below are some of the information stored in audit trial:

  • Operation performed or attempted.
  • Name of the schema object accessed.
  • TERMINAL IDENTIFIER: Machine from which the user performed the action.
  • TIMESTAMP : When the action was performed.
  • SESSION IDENTIFIER: Session which performed the activity
  • DDL STATEMENT: CREATE, ALTER & DROP of objects)
  • DML STATEMENT: INSERT UPDATE, DELETE, SELECT, EXECUTE).
  • SYSTEM EVENTS: LOGON, LOGOFF etc.

Over a period of time, sys.aud$ table will grow and it must be truncated/deleted to prevent this table to grow unacceptable size.

Command To Backup and Truncate sys.aud$ table:

SQL> create table owner.audit_hist tablespace USERS nologging parallel (degree 4) as select * from sys.aud$;

SQL> truncate table sys.aud$;

Script to Backup and Truncate SYS.AUD$ Table

Note: Test the script on test database before implementing into production. 
#!/usr/bin/ksh
#
ORACLE_SID=orahowdb
DB_NAME=orahowdb
LOGFILE=/data/audit/${DB_NAME}/logs/purge_audit.log

DBA='orahowindia@gmail.com'

. /data/app/admin/${DB_NAME}/.orauser_$ORACLE_SID
sqlplus -s "/ as sysdba" <$LOGFILE

drop table owner.audit_hist;
create table owner.audit_hist tablespace USERS nologging parallel (degree 4) as select * from sys.aud$;

truncate table sys.aud$;

!!
cat $LOGFILE | mailx -s "orahowdb: AUD$ table truncated " $DBA

Must Read: