The Ora DBAPractical Oracle Database Administration Knowledge & Solutions

Thursday, August 20, 2026

Configure Standard Auditing in Oracle Database

Oracle DBA Security Auditing Oracle 19c

Standard Auditing in Oracle Database is a security and compliance feature that records selected database activities and user actions to provide an audit trail of activity performed within the database.

It can be configured to audit activities such as SQL operations performed on database objects, database privileges, schema/object changes, and other administrative actions. Auditing can also record whether an audited operation was successful or unsuccessful and can be configured to record activity by session or by access.

The AUDIT_TRAIL Parameter

The AUDIT_TRAIL parameter controls whether and where audit records are stored.

Value 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.

Prerequisites

  • Approximately 30 minutes of downtime is required for the parameter change and database restart.
  • Sufficient space must be available at the tablespace and operating-system level for storing audit records.

Step 1: Set the AUDIT_TRAIL Parameter

Set AUDIT_TRAIL to DB,EXTENDED to store audit records in the AUD$ table along with SQL text and bind variables. This parameter change requires a database restart. 

show parameter audit_trail

alter system set audit_trail=DB,EXTENDED scope=spfile;

shut immediate

startup

show parameter audit_trail
Output
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB


SQL> alter system set audit_trail=DB,EXTENDED scope=spfile;

System altered.


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1694498312 bytes
Fixed Size                  9178632 bytes
Variable Size             419430400 bytes
Database Buffers         1258291200 bytes
Redo Buffers                7598080 bytes
Database mounted.
Database opened.


SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19c/db
                                                  _home/dbs/spfilePROD.ora


SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB, EXTENDED

Step 2: Assign a Tablespace for Audit Records

First check the current location of the standard audit table SYS.AUD$. 

SET LINESIZE 333 PAGESIZE 333

COLUMN SEGMENT_NAME FORMAT A20
COLUMN TABLE_NAME FORMAT A20

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

SELECT table_name,
       tablespace_name,
       segment_created
FROM dba_tables
WHERE owner='SYS'
AND table_name='AUD$';
Output
SQL> SELECT segment_name, tablespace_name, blocks, bytes/1024/1024 "Size Mb"
     FROM dba_segments
     WHERE segment_name IN ('AUD$');

no rows selected


SQL> SELECT table_name, tablespace_name, segment_created
     FROM dba_tables
     WHERE owner='SYS'
     AND table_name='AUD$';

TABLE_NAME           TABLESPACE_NAME                SEG
-------------------- ------------------------------ ---
AUD$                 SYSTEM                         NO

Create the Audit Tablespace

CREATE TABLESPACE audit_data
DATAFILE '+DATA'
SIZE 1G
AUTOEXTEND ON;
Output
SQL> CREATE TABLESPACE audit_data DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON;

Tablespace created.

Verify the Audit Tablespace

column file_name format a70
column tablespace_name format a20

SELECT file_name,
       tablespace_name,
       bytes/1024/1024,
       status,
       autoextensible
FROM dba_data_files
WHERE tablespace_name IN ('AUDIT_DATA');
Output
FILE_NAME                                                              TABLESPACE_NAME      BYTES/1024/1024 STATUS    AUT
---------------------------------------------------------------------- -------------------- --------------- --------- ---
+DATA/PROD/589D6350A5B241DAE0636538A8C021C3/DATAFILE/audit_data.284.12 AUDIT_DATA                      1024 AVAILABLE YES

Move the Standard Audit Trail Location

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;
/
Output
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.

Verify AUD$ Location

SELECT table_name,
       tablespace_name,
       segment_created
FROM dba_tables
WHERE owner='SYS'
AND table_name='AUD$';
Output
TABLE_NAME           TABLESPACE_NAME      SEG
-------------------- -------------------- ---
AUD$                 AUDIT_DATA           NO

Step 3: Enable Required Auditing

BY SESSION vs BY ACCESS

BY SESSION records one audit entry per audited operation type per session.

BY ACCESS records an audit entry for each execution of the audited action.

WHENEVER [NOT] SUCCESSFUL

These clauses control whether Oracle audits successful operations, unsuccessful operations, or both.

If neither clause is specified, Oracle audits both successful and unsuccessful operations. 

AUDIT TABLE BY ACCESS;
AUDIT ALTER TABLE BY ACCESS;
AUDIT CREATE TABLESPACE BY ACCESS;
AUDIT ALTER DATABASE BY ACCESS;
AUDIT ALTER SESSION BY ACCESS;
AUDIT ALTER SYSTEM BY ACCESS;
Output
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PRODPDB                        READ WRITE NO


SQL> AUDIT TABLE BY ACCESS;

Audit succeeded.


SQL> AUDIT ALTER TABLE BY ACCESS;

Audit succeeded.


SQL> AUDIT CREATE TABLESPACE BY ACCESS;

Audit succeeded.


SQL> AUDIT ALTER DATABASE BY ACCESS;

Audit succeeded.


SQL> AUDIT ALTER SESSION BY ACCESS;

Audit succeeded.


SQL> AUDIT ALTER SYSTEM BY ACCESS;

Audit succeeded.

Verify Audit Options

SET LINESIZE 333 PAGESIZE 333

SELECT audit_option,
       success,
       failure
FROM dba_stmt_audit_opts;
Output
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS
ALTER SESSION                            BY ACCESS  BY ACCESS
TABLE                                    BY ACCESS  BY ACCESS
CREATE TABLESPACE                        BY ACCESS  BY ACCESS
ALTER TABLE                              BY ACCESS  BY ACCESS
ALTER DATABASE                           BY ACCESS  BY ACCESS

6 rows selected.

Step 4: Verify Standard Audit Records

After enabling the required auditing, query DBA_AUDIT_TRAIL to verify that audit records are being generated. 

SET LINESIZE 150 PAGESIZE 100

COLUMN username FORMAT A15
COLUMN owner FORMAT A15
COLUMN obj_name FORMAT A25
COLUMN action_name FORMAT A20
COLUMN extended_timestamp FORMAT A35
COLUMN sql_text FORMAT A50

SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name,
       sql_text
FROM dba_audit_trail
ORDER BY extended_timestamp;
Output
SQL> CREATE USER AUDIT_TEST IDENTIFIED BY "Password123";
SQL> GRANT CREATE SESSION TO AUDIT_TEST;

User created.

Grant succeeded.


SQL> exit

[oracle@production-vm ~]$ sqlplus AUDIT_TEST/Password123@PRODPDB

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0


SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.


SQL> SELECT username,
            extended_timestamp,
            owner,
            obj_name,
            action_name,
            sql_text
     FROM dba_audit_trail
     ORDER BY extended_timestamp;

USERNAME        EXTENDED_TIMESTAMP                  OWNER           OBJ_NAME                  ACTION_NAME          SQL_TEXT
--------------- ----------------------------------- --------------- ------------------------- -------------------- ------------------------------------------------------------
AUDIT_TEST      09-08-26 9:05:27.553452 PM +05:30                                             ALTER SESSION        ALTER SESSION SET TIME_ZONE='+05:30'
AUDIT_TEST      09-08-26 9:05:38.206291 PM +05:30                                             ALTER SESSION        ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'
Standard Auditing is working successfully.

The audit options are enabled with BY ACCESS, and audit activity generated by the test session is visible in DBA_AUDIT_TRAIL.

Steps to Disable Standard Auditing

If the auditing configuration is no longer required, the audit options enabled with the AUDIT statements can be disabled using the corresponding NOAUDIT statements. The NOAUDIT statement reverses the effect of the applicable AUDIT statement.

For the audit options enabled in this procedure, use: 

NOAUDIT TABLE;
NOAUDIT ALTER TABLE;
NOAUDIT CREATE TABLESPACE;
NOAUDIT ALTER DATABASE;
NOAUDIT ALTER SESSION;
NOAUDIT ALTER SYSTEM;
Important: NOAUDIT disables the specified audit options. It does not by itself disable the AUDIT_TRAIL initialization parameter. If the objective is to stop traditional database auditing configured through AUDIT_TRAIL, the parameter must also be changed.

Verify the Audit Options Are Disabled

SET LINESIZE 333 PAGESIZE 333

SELECT audit_option,
       success,
       failure
FROM dba_stmt_audit_opts
ORDER BY audit_option;
Output
No rows selected.

Disable Traditional Auditing Completely

If traditional auditing is no longer required, set AUDIT_TRAIL to NONE in the server parameter file and restart the database. Because the parameter is a static initialization parameter, a restart is required for the change to take effect. 

SHOW PARAMETER AUDIT_TRAIL

ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

SHUT IMMEDIATE;

STARTUP;

SHOW PARAMETER AUDIT_TRAIL;
Output
SQL> ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

System altered.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Database mounted.
Database opened.

SQL> SHOW PARAMETER AUDIT_TRAIL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE
Note: Setting AUDIT_TRAIL=NONE disables traditional auditing controlled by this parameter. It does not mean that every possible Oracle auditing mechanism is disabled. In Oracle Database 19c, Unified Auditing is a separate auditing framework and enabled unified audit policies should be reviewed separately.

Final Verification

After disabling the required audit options, verify both the statement audit configuration and the AUDIT_TRAIL parameter. 

SELECT audit_option,
       success,
       failure
FROM dba_stmt_audit_opts;

SHOW PARAMETER AUDIT_TRAIL;

The audit options configured in this procedure should no longer appear in DBA_STMT_AUDIT_OPTS, and if traditional auditing has been disabled completely, AUDIT_TRAIL should show NONE.

Summary

The procedure configures Standard Auditing by setting AUDIT_TRAIL=DB,EXTENDED, creating a dedicated AUDIT_DATA tablespace, moving the standard audit trail to that tablespace, enabling selected audit options, and finally validating the generated records through DBA_AUDIT_TRAIL.

at
Labels: , ,
Location: Noida, Uttar Pradesh, India