Oracle Database Auditing

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • 10 Sep, 2023
  • 2 Mins Read

Oracle Database Auditing

Oracle Database Auditing


Oracle database auditing : It  allows you to monitor certain database actions happening inside the database. Auditing also helps in looking at the actions performed against a particular table, schema or certain specific rows.   To check database auditing is enabled execute SHOW PARAMETER command
Show parameter audit;
The AUDIT_TRAIL parameter defines database auditing status. It is having following values
  • None – Database auditing is disabled
  • os – Enabled, audit logs are stored at OS level, not inside the database
  • db – Enabled, audit records are stored inside database (SYS.AUD$ table). If we are creating database by DBCA Utility , then default it is DB else it is none.
  • db,extended – Same as db but populates SQL_BIND & SQL_TEXT too
  • xml – Enabled, audit records are stored at OS level in XML format
  • xml,extended – Same as xml but populates SQL_BIND & SQL_TEXT too
To change or update AUDI_TRAIL parameter status, use below command :
alter system set AUDIT_TRAIL=<status value>, extended scope=spfile;
Please Note : Here status value indicates any of value mentioned above. example :
alter system set AUDIT_TRAIL= db, extended scope=spfile;

If auditing is not enabled, the Oracle Database Adapter cannot retrieve the information about when the user last accessed the account.

To enable auditing , follow the below steps :

  1. Set the initialization parameter audit_trail to TRUE in the init.ora file (pfile).
  2.  issue the following command at the SQL prompt:  ALTER SYSTEM SET audit_trail=TRUE scope=SPFILE.
  3. Restart the database instance.
  4. To turn on the auditing for user  log on as a user with  admin authority and Issue the following command at the SQL  prompt: AUDIT CONNECT.
  5. To verify that auditing is enabled on an instance, issue the following command at the SQL  prompt: SHOW PARAMETER AUDIT_TRAIL.
P.S. : Any value except NONE or FALSE indicates that auditing is enabled.


The AUDIT_FILE_DEST parameter defines the OS level location of the audit trail files. By default it is set to adump location.

All the SYS operations audit records goes to OS level into AUDIT_FILE_DEST location. audit_file_dest  default Location is  $ORACLE_HOME/rdbms/audit.  The default value is: ORACLE_BASE / admin/ ORACLE_SID/adump.

This create files with the .aud extentionto audit operations.

If you set audit_sys_operations=true.

There is a separate file for every session that connects as SYSDBA.

SQL> show parameter audit

NAME                   TYPE          VALUE
----------------------- ----------- --------------
audit_file_dest          string      /u01/app/oracle/admin/prim/audit
audit_sys_operations     boolean     TRUE
audit_trail              string      DB