Oracle Database Standard Auditing

Introduction

Oracle Database Auditing is a crucial tool that helps database administrators monitor and record activities occurring within the database environment. By tracking actions such as data modifications, user access, and system changes, auditing provides a detailed record of who did what and when. This is the first post in a series on Oracle auditing, and it covers Standard Auditing: what it is, how it compares to the other auditing types, and how to configure it step by step.

Types of Auditing in Oracle

Oracle supports four distinct approaches to auditing, each suited to different needs.

Mandatory Auditing

Certain critical actions and users, such as SYSDBA, are audited by default with no configuration required, ensuring that the most sensitive operations are always tracked. This includes:

• Logons by privileged users connecting with SYSDBA, SYSOPER, or similar administrative roles.
• Startup and shutdown operations of the Oracle instance.
• DDL and DML statements affecting the data dictionary and audit infrastructure.
• Direct interactions with audit-related system tables, such as the unified audit trail tables.

Standard Auditing

This traditional auditing method tracks specific SQL statements and privileges. You can audit actions like SELECT, INSERT, UPDATE, DELETE, DDL commands, and user logins by enabling audit policies with the AUDIT statement.

Fine-Grained Auditing (FGA)

FGA provides more targeted auditing by letting you define specific conditions for when access should be audited. For example, you can audit access to particular columns or rows based on user-defined predicates, which is useful for monitoring sensitive data access.

Unified Auditing

Introduced in Oracle 12c, Unified Auditing consolidates audit records from every source into a single, centralized audit trail. It simplifies audit management and improves performance by unifying standard, fine-grained, and other audit records under one system.

The table below compares these four approaches.

Feature	Mandatory Auditing	Standard Auditing	Fine-Grained Auditing (FGA)	Unified Auditing
Purpose	Audit critical system activities and SYSDBA/SYSOPER actions	Audit general actions (DML, DDL, logins)	Audit access to specific rows/columns based on conditions	Consolidates all audit types into one unified audit trail
Configuration	Auto-enabled	AUDIT / NOAUDIT	DBMS_FGA package	CREATE AUDIT POLICY + AUDIT statements
Granularity	System-level	Statement-level	Row & column with conditions	All levels unified
Where Records Are Stored	UNIFIED_AUDIT_TRAIL or OS	DBA_AUDIT_TRAIL or OS files	DBA_FGA_AUDIT_TRAIL	UNIFIED_AUDIT_TRAIL view (secure internal table)
Available In	Both Editions	Standard & Enterprise	Enterprise Only	Enterprise Only
Disablable	No	Yes	Yes	Yes (via policy management)

The AUDIT_TRAIL Parameter

The AUDIT_TRAIL parameter controls whether and where audit records are stored, and it's essential for enabling auditing. The table below covers its possible values.

Option	Description
NONE	Disables auditing. No audit records are generated.
OS	Audit records are written to operating system files.
DB	Audit records are written to the database table AUD$ in the SYS schema.
DB,EXTENDED	Same as DB, but includes SQL statements and bind variables.
XML	Audit records are written to XML files in the location defined by AUDIT_FILE_DEST.
XML,EXTENDED	Same as XML, but includes SQL text and bind values.

Mandatory Auditing needs no configuration since it's enabled by default, so this article focuses on setting up Standard Auditing.

Prerequisites

• 30 minutes of downtime.
• Sufficient space at the tablespace and OS level for storing audit records.

Environment Used in This Guide

Hostname	orcl.oraeasy.com
Database Name	orcldb
Database Version	19.27
Database Edition	Enterprise Edition

Step 1: Set the AUDIT_TRAIL Parameter

We'll set AUDIT_TRAIL to DB,EXTENDED, which stores records in the AUD$ table along with the actual SQL text and bind variables. This parameter change requires a database bounce.

First, Create a Pfile:

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL>
SQL> DEF
DEFINE _DATE           = "30-JUL-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1927000000" (CHAR)
SQL>
SQL> CREATE PFILE='/home/oracle/pfileorcl.ora' FROM SPFILE;

File created.

Set the AUDIT_TRAIL Parameter (Requires a Database Bounce):

SQL> SHOW PARAMETER audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
                                                 mp
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_common_systemlog       string
unified_audit_sga_queue_size         integer     1048576
unified_audit_systemlog              string
SQL>
SQL> ALTER SYSTEM SET audit_trail=DB, EXTENDED SCOPE=SPFILE;

System altered.

SQL>
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP
ORACLE instance started.

Total System Global Area  524284552 bytes
Fixed Size                  9179784 bytes
Variable Size             432013312 bytes
Database Buffers           75497472 bytes
Redo Buffers                7593984 bytes
Database mounted.
Database opened.
SQL>
SQL> SHOW PARAMETER audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
                                                 mp
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB, EXTENDED
unified_audit_common_systemlog       string
unified_audit_sga_queue_size         integer     1048576
unified_audit_systemlog              string
SQL>
SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL>
SQL> ALTER SESSION SET CONTAINER=ORCLPDB;

Session altered.

SQL>
SQL> SHOW PARAMETER audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
                                                 mp
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB, EXTENDED
unified_audit_common_systemlog       string
unified_audit_sga_queue_size         integer     1048576
unified_audit_systemlog              string
SQL>

Step 2: Assign a Tablespace for Audit Data

By default, the AUD$ table lives in the SYSTEM tablespace. Moving it to a dedicated tablespace keeps audit growth from crowding SYSTEM, and is done through the DBMS_AUDIT_MGMT package.

SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN SEGMENT_NAME FORMAT A20
SQL> SELECT segment_name, tablespace_name, blocks, bytes/1024/1024 "Size Mb"
     FROM dba_segments
     WHERE segment_name IN ('AUD$');

SEGMENT_NAME         TABLESPACE_NAME      BLOCKS    Size Mb
-------------------- -----------------  -------- ----------
AUD$                 SYSTEM                    8      .0625

SQL> COLUMN FILE_NAME FORMAT A80
SQL> SELECT file_name, tablespace_name, bytes/1024/1024, status, autoextensible
     FROM dba_data_files
     WHERE tablespace_name IN ('SYSTEM');

FILE_NAME                                            TABLESPACE_NAME    BYTES/1024/1024 STATUS    AUT
---------------------------------------------------- ------------------ --------------- --------- ---
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf    SYSTEM                         680 AVAILABLE YES

SQL> CREATE TABLESPACE audit_data 
     DATAFILE '/u01/app/oracle/oradata/ORCL/orclpdb/audit_data01.dbf' 
     SIZE 1G AUTOEXTEND ON;

Tablespace created.

SQL> SELECT file_name, tablespace_name, bytes/1024/1024, status, autoextensible
     FROM dba_data_files
     WHERE tablespace_name IN ('AUDIT_DATA');

FILE_NAME                                                TABLESPACE_NAME   BYTES/1024/1024 STATUS    AUT
-------------------------------------------------------- ----------------- --------------- --------- ---
/u01/app/oracle/oradata/ORCL/orclpdb/audit_data01.dbf    AUDIT_DATA                   1024 AVAILABLE YES

SQL>
SQL> BEGIN
         dbms_audit_mgmt.set_audit_trail_location(
             audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
             audit_trail_location_value => 'AUDIT_DATA');
     END;
     /
PL/SQL procedure successfully completed.

SQL> SELECT segment_name, tablespace_name, blocks, bytes/1024/1024 "Size Mb"
     FROM dba_segments
     WHERE segment_name IN ('AUD$');

SEGMENT_NAME         TABLESPACE_NAME       BLOCKS    Size Mb
-------------------- ----------------- ---------- ----------
AUD$                 AUDIT_DATA                 8      .0625

Step 3: Enable the Audit Policy

With storage in place, enable the actual audit policy. Here we'll audit DDL activity: table, index, procedure, view, and trigger operations.

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
--- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL>

SQL> ALTER SESSION SET CONTAINER=ORCLPDB;

Session altered.

SQL> SELECT COUNT(*) FROM dba_audit_trail;

  COUNT(*)
----------
         0

SQL> AUDIT TABLE BY ACCESS;

Audit succeeded.

SQL> AUDIT ALTER TABLE BY ACCESS;

Audit succeeded.

SQL> AUDIT INDEX BY ACCESS;

Audit succeeded.

SQL> AUDIT PROCEDURE BY ACCESS;

Audit succeeded.

SQL> AUDIT VIEW BY ACCESS;

Audit succeeded.

SQL> AUDIT TRIGGER BY ACCESS;

Audit succeeded.

SQL>

Step 4: Verify the Enabled Audit Statements

SQL> SET LINESIZE 333 PAGESIZE 333
SQL> SELECT audit_option, success, failure
     FROM dba_stmt_audit_opts;

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
TABLE                                    BY ACCESS  BY ACCESS
VIEW                                     BY ACCESS  BY ACCESS
PROCEDURE                                BY ACCESS  BY ACCESS
TRIGGER                                  BY ACCESS  BY ACCESS
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
INDEX                                    BY ACCESS  BY ACCESS
ALTER TABLE                              BY ACCESS  BY ACCESS
CREATE ANY INDEX                         BY ACCESS  BY ACCESS
DROP ANY INDEX                           BY ACCESS  BY ACCESS

9 rows selected.

SQL>

Step 5: Create a Test User

SQL> CREATE USER testaudit IDENTIFIED BY TesT##123;

User created.

SQL> GRANT connect, resource TO testaudit;

Grant succeeded.

SQL> GRANT unlimited tablespace TO testaudit;

Grant succeeded.

SQL> GRANT create view TO testaudit;

Grant succeeded.

Step 6: Perform DDL Operations as the Test User

Log in as the test user and run a mix of DDL statements to generate audit records.

SQL> DEF
DEFINE _DATE           = "30-JUL-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orclpdb" (CHAR)
DEFINE _USER           = "TESTAUDIT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1927000000" (CHAR)
SQL>
SQL> CREATE TABLE company (emp_id INT, name VARCHAR2(255));

Table created.

SQL> DROP TABLE company;

Table dropped.

SQL> CREATE TABLE company (emp_id INT, name VARCHAR2(255));

Table created.

SQL> ALTER TABLE COMPANY DROP COLUMN city;

Table altered.

SQL> ALTER TABLE company ADD city VARCHAR2(20);

Table altered.

SQL> CREATE INDEX testidx ON company(city);

Index created.

SQL> DROP INDEX testidx;

Index dropped.

SQL> CREATE INDEX testidx ON company (city);

Index created.

SQL> ALTER INDEX testidx REBUILD;

Index altered.

SQL> ALTER INDEX testidx RENAME TO testidx_renamed;

Index altered.

SQL> CREATE VIEW comview AS SELECT * FROM company;

View created.

SQL> CREATE OR REPLACE TRIGGER trg_before_insert_company
	 BEFORE INSERT ON company
	 FOR EACH ROW
 	 BEGIN
 	 :NEW.name := UPPER(:NEW.name);
   	 END;
	 / 

Trigger created.

SQL> CREATE OR REPLACE PROCEDURE add_employee (
 	 p_emp_id IN NUMBER,
 	 p_name   IN VARCHAR2
	)
	IS
	BEGIN
  	INSERT INTO company (emp_id, name)
  	VALUES (p_emp_id, p_name);
 	DBMS_OUTPUT.PUT_LINE('Employee added: ' || p_emp_id || ' - ' || p_name);
    EXCEPTION
  	WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Error: Employee with ID '   3  || p_emp_id || ' already exists.');
  	WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
	END;
	/

Procedure created.

Step 7: Review the Captured Audit Data

SQL> SET LINESIZE 150 PAGESIZE 100
SQL> COLUMN username FORMAT A15
SQL> COLUMN owner FORMAT A15
SQL> COLUMN obj_name FORMAT A25
SQL> COLUMN action_name FORMAT A20
SQL> COLUMN extended_timestamp FORMAT A35
SQL> SELECT username, extended_timestamp, owner, obj_name, action_name
     FROM dba_audit_trail
     ORDER BY extended_timestamp;

USERNAME             EXTENDED_TIMESTAMP                       OWNER                OBJ_NAME                                 ACTION_NAME
-------------------- ---------------------------------------- -------------------- ---------------------------------------- ----------------------------
TESTAUDIT            30-JUL-25 09.20.09.495217 PM +05:30      TESTAUDIT            COMPANY                                  CREATE TABLE
TESTAUDIT            30-JUL-25 09.22.34.542501 PM +05:30      TESTAUDIT            COMPANY                                  DROP TABLE
TESTAUDIT            30-JUL-25 09.22.51.495226 PM +05:30      TESTAUDIT            COMPANY                                  CREATE TABLE
TESTAUDIT            30-JUL-25 09.32.34.970492 PM +05:30      TESTAUDIT            COMPANY                                  ALTER TABLE
TESTAUDIT            30-JUL-25 09.35.11.064356 PM +05:30      TESTAUDIT            COMPANY                                  ALTER TABLE
TESTAUDIT            30-JUL-25 09.39.03.514319 PM +05:30      TESTAUDIT            TESTIDX                                  DROP INDEX
TESTAUDIT            30-JUL-25 09.39.29.215912 PM +05:30      TESTAUDIT            TESTIDX                                  CREATE INDEX
TESTAUDIT            30-JUL-25 09.42.37.492291 PM +05:30      TESTAUDIT            TESTIDX                                  CREATE INDEX
TESTAUDIT            30-JUL-25 09.43.00.302514 PM +05:30      TESTAUDIT            TESTIDX                                  ALTER INDEX
TESTAUDIT            30-JUL-25 10.09.23.738087 PM +05:30      TESTAUDIT            COMVIEW                                  CREATE VIEW
TESTAUDIT            30-JUL-25 10.09.42.569093 PM +05:30      TESTAUDIT            COMVIEW                                  CREATE VIEW
TESTAUDIT            30-JUL-25 10.12.37.288751 PM +05:30      TESTAUDIT            TRG_BEFORE_INSERT_COMPANY                CREATE TRIGGER
TESTAUDIT            30-JUL-25 10.13.21.758069 PM +05:30      TESTAUDIT            ADD_EMPLOYEE                             CREATE PROCEDURE

13 rows selected.

Every DDL statement from Step 6 shows up here with the username, timestamp, and exact action, confirming the policy is working as expected.

Step 8: Enable DML Auditing

DML auditing follows the same pattern, scoped to a specific table.

SQL> AUDIT INSERT, UPDATE, DELETE ON testaudit.company BY ACCESS;

Audit succeeded.

Check the DML Audit Policy:

SQL> SELECT owner, object_name, ins, upd, del FROM   dba_obj_audit_opts
	WHERE  owner = 'TESTAUDIT' AND object_name = 'COMPANY';
    
OWNER                OBJECT_NAME          INS       UPD       DEL
-------------------- -------------------- --------- --------- ---------
TESTAUDIT            COMPANY              A/A       A/A       A/A

SQL>

Step 9: Disable Auditing for an Object

To turn off auditing for a specific object or statement type, use the NOAUDIT statement.

SQL> NOAUDIT INSERT, UPDATE, DELETE ON testaudit.company;

Noaudit succeeded.

SQL>
SQL> NOAUDIT VIEW;

Noaudit succeeded.

SQL>

Purging Audit Data

Left unmanaged, the audit trail grows indefinitely. The DBMS_AUDIT_MGMT package handles purging old records cleanly, without needing to touch the underlying table directly.

Step 1: Check the Current Audit Record Count

SQL> SELECT COUNT(*) FROM dba_audit_trail;

  COUNT(*)
----------
        15

SQL> SELECT COUNT(*) FROM dba_audit_trail WHERE  timestamp < SYSTIMESTAMP - 10;

  COUNT(*)
----------
        13
        

Step 2: Set the LAST_ARCHIVE_TIMESTAMP

This tells DBMS_AUDIT_MGMT the cutoff point: anything older than this timestamp is treated as safe to purge.

SQL> BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-10);
END;
/ 

PL/SQL procedure successfully completed.

SQL>

SQL> COLUMN audit_trail FORMAT A20
SQL> COLUMN last_archive_ts FORMAT A40
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS                          DATABASE_ID CONTAINER_GUID
-------------------- ------------ ---------------------------------------- ----------- ---------------------------------
FGA AUDIT TRAIL                 0 31-JUL-25 10.56.47.000000 PM +00:00       1593504508 2C3D6BACF692345BE0633350A8C050ED
STANDARD AUDIT TRAIL            0 03-AUG-25 07.46.26.000000 PM +00:00       1593504508 2C3D6BACF692345BE0633350A8C050ED

Step 3: Purge Records Older Than the Archive Timestamp

SQL> BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/ 

PL/SQL procedure successfully completed.

SQL>
        

Step 4: Verify the Purge

SQL> SELECT COUNT(*) FROM dba_audit_trail;

  COUNT(*)
----------
         2

SQL> SELECT COUNT(*) FROM dba_audit_trail WHERE timestamp < SYSTIMESTAMP - 10;

  COUNT(*)
----------
         0
SQL>        

Alternative: Purge Directly on sys.aud$

You can also delete or truncate the underlying sys.aud$ table directly, though the DBMS_AUDIT_MGMT approach above is the safer, supported path for routine housekeeping.

To Purge Records Older Than 30 Days:

SQL> DELETE FROM sys.aud$ WHERE ntimestamp# < SYSTIMESTAMP - INTERVAL '30' DAY;
SQL> COMMIT;

To Empty the Audit Record Table Entirely:

SQL> TRUNCATE TABLE sys.aud$;

That covers Standard Auditing end to end: enabling it, scoping it to DDL and DML, and keeping the audit trail from growing unbounded. The next post in this series will look at Fine-Grained and Unified Auditing.

Thank you for reading!

I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.

Abhishek Shrivastava

Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

Follow Us