The Ora DBAPractical Oracle Database Administration Knowledge & Solutions

Friday, August 21, 2026

Configure Unified Auditing in Oracle Database

Oracle Database 19c Unified Auditing AUD$UNIFIED CREATE AUDIT POLICY UNIFIED_AUDIT_TRAIL

Unified Auditing in Oracle Database is a centralized auditing framework that records database activity through unified audit policies and the unified audit trail. It provides a consolidated mechanism for auditing user actions, SQL operations, administrative activities, and selected database events.

Unified Auditing can be used to audit selected database actions and administrative operations, DML activity on specific database objects, and successful or failed operations depending on the policy configuration. Audit records are stored in the unified audit trail and can be queried through UNIFIED_AUDIT_TRAIL.

This guide demonstrates how to verify Unified Auditing, enable it using the uniaud_on target, configure the unified audit trail in a dedicated AUDIT_DATA tablespace, create DDL and DML audit policies, test those policies, and verify the generated audit records.

Environment used in this guide: Oracle Database 19c Enterprise Edition, Version 19.28.0.0.0.

Unified Auditing Architecture

Component Purpose Verification / Configuration
Unified Auditing Centralized auditing framework V$OPTION → Unified Auditing = TRUE
Unified Audit Trail Stores unified audit records AUDSYS.AUD$UNIFIED / UNIFIED_AUDIT_TRAIL
Audit Policy Defines actions to audit CREATE AUDIT POLICY
Enabled Policy Controls whether a policy is active AUDIT POLICY / AUDIT_UNIFIED_ENABLED_POLICIES
Audit Trail Location Defines the tablespace for the unified trail DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION

The unified audit records are stored in AUD$UNIFIED and exposed through UNIFIED_AUDIT_TRAIL. In this implementation, the audit trail was initially located in SYSAUX and was subsequently relocated to the dedicated AUDIT_DATA tablespace.

The AUDIT_TRAIL Parameter

The AUDIT_TRAIL parameter controls whether and where traditional audit records are stored. The following values were reviewed as part of the implementation.

AUDIT_TRAIL 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.
Important: The AUDIT_TRAIL parameter relates to traditional auditing. Unified Auditing uses the unified audit trail stored in AUDSYS.AUD$UNIFIED.

Prerequisites

  • SYS with SYSDBA access.
  • A controlled database restart window because the configuration changes the Oracle executable and AUDIT_TRAIL parameter.
  • Sufficient database and operating-system space for audit records.
  • A dedicated AUDIT_DATA tablespace with sufficient capacity.
  • For a Data Guard environment, the corresponding Unified Auditing relinking procedure should also be performed on the DR database.
Production consideration: Unified Auditing can generate a significant number of records depending on the number and scope of enabled audit policies. Review audit volume, tablespace capacity, retention, and cleanup requirements before enabling broad policies in production.

Step 1: Check the Existing Audit Configuration

Before making changes, verify the current AUDIT_TRAIL setting. 

SQL> show parameter audit_trail
Output
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

Configure the traditional audit trail parameter as DB,EXTENDED. 

SQL> alter system set audit_trail=DB,EXTENDED scope=spfile;
Output
System altered.

Because the parameter is stored in the SPFILE, the database must be restarted to apply the change. 

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

Step 2: Enable Unified Auditing by Relinking the Oracle Executable

Unified Auditing was enabled by relinking the Oracle database executable using the uniaud_on target.

Stop the listener before performing the relinking operation. 

$ sudo su - grid
$ lsnrctl stop LISTENER
Output
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-AUG-2026 19:05:58

Copyright (c) 1991, 2026, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=production-vm)(PORT=1521)))

The command completed successfully

Return to the appropriate operating-system user and move to the Oracle database library directory. 

$ exit
$ cd $ORACLE_HOME/rdbms/lib
$ cp ins_rdbms.mk ins_rdbms.mk_bkp
Output
Logout

Execute the Unified Auditing relink target. 

$ make -f ins_rdbms.mk uniaud_on ioracle
Output
/usr/bin/ar d /u01/app/oracle/product/19c/db_home/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/19c/db_home/rdbms/lib/libknlopt.a /u01/app/oracle/product/19c/db_home/rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/19c/db_home/bin
cd /u01/app/oracle/product/19c/db_home/rdbms/lib/;\
/usr/bin/ar r /u01/app/oracle/product/19c/db_home/rdbms/lib/libknlopt.a `/usr/bin/ar t /u01/app/oracle/product/19c/db_home/rdbms/lib/libknlopt.a` ;
- Linking Oracle
rm -f /u01/app/oracle/product/19c/db_home/rdbms/lib/oracle
...
rm -f /u01/app/oracle/product/19c/db_home/bin/oracle
mv /u01/app/oracle/product/19c/db_home/rdbms/lib/oracle /u01/app/oracle/product/19c/db_home/bin/oracle
chmod 6751 /u01/app/oracle/product/19c/db_home/bin/oracle
Result: The Oracle executable was successfully relinked with the uniaud_on target.

Step 3: Start the Database and Verify Unified Auditing

Start the database after completing the relinking operation. 

SQL> startup
Output
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.

Verify the PDB configuration. 

SQL> show pdbs
Output
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PRODPDB                        READ WRITE NO

Verify the AUDIT_TRAIL parameter. 

SQL> show parameter audit_trail
Output
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB, EXTENDED

Step 4: Verify Unified Auditing Status

The V$OPTION view can be used to verify whether Unified Auditing is active in the Oracle executable. 

SQL> SELECT value
     FROM v$option
     WHERE parameter = 'Unified Auditing';
Output
VALUE
----------------------------------------------------------------
TRUE
Result: V$OPTION confirms that Unified Auditing is enabled.

Step 5: Verify the Unified Audit Trail

Switch to the application PDB and identify the current location of AUD$UNIFIED. 

SQL> ALTER SESSION SET CONTAINER=PRODPDB;

SQL> SET LINESIZE 333
SQL> SET PAGESIZE 333

SQL> COLUMN SEGMENT_NAME FORMAT A20
SQL> COLUMN TABLE_NAME FORMAT A20

SQL> SELECT segment_name,
            tablespace_name,
            blocks,
            bytes/1024/1024 "Size Mb"
     FROM dba_segments
     WHERE segment_name IN ('AUD$UNIFIED');
Output
SEGMENT_NAME         TABLESPACE_NAME                BLOCKS    Size Mb
-------------------- ------------------------------ ---------- ----------
AUD$UNIFIED          SYSAUX                         1152       9

The output shows that AUD$UNIFIED was initially located in the SYSAUX tablespace.

Step 6: Create a Dedicated AUDIT_DATA Tablespace

A dedicated tablespace can be used to separate unified audit records from the standard Oracle system tablespaces. 

CREATE TABLESPACE AUDIT_DATA
DATAFILE '+DATA'
SIZE 1G
AUTOEXTEND ON;
Output
Tablespace created.

Verify the datafile and autoextend configuration. 

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

Step 7: Move the Unified Audit Trail to AUDIT_DATA

Use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION to configure the tablespace location of the unified audit trail. 

BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
        audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        audit_trail_location_value => 'AUDIT_DATA'
    );
END;
/
Output
PL/SQL procedure successfully completed.

Verify the default tablespace of the interval-partitioned AUDSYS.AUD$UNIFIED table. 

SET LINESIZE 333
SET PAGESIZE 333

COLUMN owner FORMAT A15
COLUMN table_name FORMAT A20
COLUMN interval FORMAT A30

SELECT owner,
       table_name,
       interval,
       partitioning_type,
       partition_count,
       def_tablespace_name
FROM dba_part_tables
WHERE owner = 'AUDSYS';
Output
OWNER           TABLE_NAME           INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
--------------- -------------------- ------------------------------ --------- --------------- ------------------------------
AUDSYS          AUD$UNIFIED          INTERVAL '1' MONTH             RANGE     1048575         AUDIT_DATA
Result: The default tablespace for the interval partitions of AUDSYS.AUD$UNIFIED is now AUDIT_DATA.

Step 8: Create the DDL Audit Policy

Create a Unified Audit Policy for important DDL and privilege-management activities. The policy condition excludes the SYS user from these records. 

CREATE AUDIT POLICY ddl_audit_policy
 ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE,
 CREATE VIEW, DROP VIEW,
 CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE,
 CREATE SEQUENCE, DROP SEQUENCE,
 CREATE TRIGGER, DROP TRIGGER,
 CREATE USER, DROP USER, ALTER USER,
 CREATE ROLE, DROP ROLE, GRANT, REVOKE,
 CREATE SYNONYM, DROP SYNONYM
 WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''SYS'''
 EVALUATE PER STATEMENT;
Output
Audit policy created.

Enable the policy. 

AUDIT POLICY ddl_audit_policy;
Output
Audit succeeded.

Step 9: Verify the Test Objects

The Unified Auditing implementation uses the existing TESTUSER test objects. 

select owner,table_name
from dba_tables
where owner like '%TEST%';
Output
OWNER              TABLE_NAME
---------------    --------------------
TESTUSER            CUSTOMERS
TESTUSER            TEST_DATA

Step 10: Create Object-Level DML Audit Policies

Create a policy to audit INSERT, UPDATE, and DELETE operations on TESTUSER.CUSTOMERS. 

CREATE AUDIT POLICY testuser_customer_dml_audit
 ACTIONS
 INSERT ON TESTUSER.CUSTOMERS,
 UPDATE ON TESTUSER.CUSTOMERS,
 DELETE ON TESTUSER.CUSTOMERS;
Output
Audit policy created.

Create the equivalent policy for TESTUSER.TEST_DATA. 

CREATE AUDIT POLICY testuser_testdata_dml_audit
 ACTIONS
 INSERT ON TESTUSER.TEST_DATA,
 UPDATE ON TESTUSER.TEST_DATA,
 DELETE ON TESTUSER.TEST_DATA;
Output
Audit policy created.

Enable both policies. 

AUDIT POLICY testuser_customer_dml_audit;

AUDIT POLICY testuser_testdata_dml_audit whenever successful;
Output
Audit succeeded.
Audit succeeded.

Step 11: Verify Enabled Unified Audit Policies

Use AUDIT_UNIFIED_ENABLED_POLICIES to verify which policies are enabled and whether successful and failed operations are being recorded. 

SET LINESIZE 333
SET PAGESIZE 333

COLUMN policy_name FORMAT A27
COLUMN entity_name FORMAT A15
COLUMN success FORMAT A10
COLUMN failure FORMAT A10

SELECT *
FROM audit_unified_enabled_policies;
Output
POLICY_NAME                  ENABLED_OPTION  ENTITY_NAME     ENTITY_ SUCCESS    FAILURE
---------------------------  --------------- --------------- ------- ---------- ----------
ORA_SECURECONFIG             BY USER         ALL USERS      USER    YES         YES
ORA_LOGON_FAILURES           BY USER         ALL USERS      USER    NO          YES
DDL_AUDIT_POLICY             BY USER         ALL USERS      USER    YES         YES
TESTUSER_CUSTOMER_DML_AUDIT BY USER         ALL USERS      USER    YES         YES
TESTUSER_TESTDATA_DML_AUDIT  BY USER         ALL USERS      USER    YES         NO

Step 12: Verify the DDL Audit Policy

The AUDIT_UNIFIED_POLICIES view can be used to review the actions defined in the policy. 

COLUMN audit_condition FORMAT A50
COLUMN object_name FORMAT A20
COLUMN audit_option FORMAT A30

SELECT policy_name,
       audit_condition,
       object_name,
       object_type,
       audit_option
FROM audit_unified_policies
WHERE policy_name = 'DDL_AUDIT_POLICY';
Output
POLICY_NAME                 AUDIT_CONDITION                              OBJECT_NAME OBJECT_TYPE AUDIT_OPTION
-------------------------   -------------------------------------------  ----------- ----------- ------------------------
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE TABLE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP TABLE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE SEQUENCE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        ALTER TABLE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP SEQUENCE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE SYNONYM
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP SYNONYM
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE VIEW
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP VIEW
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE PROCEDURE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        ALTER PROCEDURE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        ALTER USER
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE USER
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE ROLE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP USER
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP ROLE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        CREATE TRIGGER
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP TRIGGER
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        DROP PROCEDURE
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        GRANT
DDL_AUDIT_POLICY            SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS' NONE        NONE        REVOKE

Step 13: Verify the Object-Level DML Policies

Verify that INSERT, UPDATE, and DELETE are explicitly associated with the intended objects. 

SELECT policy_name,
       audit_condition,
       object_name,
       object_type,
       audit_option
FROM audit_unified_policies
WHERE policy_name = 'TESTUSER_CUSTOMER_DML_AUDIT';
Output
POLICY_NAME                  AUDIT_CONDITION OBJECT_NAME OBJECT_TYPE AUDIT_OPTION
--------------------------- --------------- ----------- ----------- -------------
TESTUSER_CUSTOMER_DML_AUDIT  NONE            CUSTOMERS   TABLE       DELETE
TESTUSER_CUSTOMER_DML_AUDIT  NONE            CUSTOMERS   TABLE       INSERT
TESTUSER_CUSTOMER_DML_AUDIT  NONE            CUSTOMERS   TABLE       UPDATE

Verify the TEST_DATA policy. 

SELECT policy_name,
       audit_condition,
       object_name,
       object_type,
       audit_option
FROM audit_unified_policies
WHERE policy_name = 'TESTUSER_TESTDATA_DML_AUDIT';
Output
POLICY_NAME                  AUDIT_CONDITION OBJECT_NAME OBJECT_TYPE AUDIT_OPTION
--------------------------- --------------- ----------- ----------- -------------
TESTUSER_TESTDATA_DML_AUDIT  NONE            TEST_DATA   TABLE       DELETE
TESTUSER_TESTDATA_DML_AUDIT  NONE            TEST_DATA   TABLE       INSERT
TESTUSER_TESTDATA_DML_AUDIT  NONE            TEST_DATA   TABLE       UPDATE
Result: Both object-level DML policies are explicitly configured for INSERT, UPDATE, and DELETE on their intended tables.

Step 14: Generate Test Audit Activity Using TESTUSER

The audit policies are tested using TESTUSER so that the resulting audit records can be associated with a normal database user rather than SYS. The test includes DML, DDL, GRANT, and REVOKE activity. 

SQL> connect TESTUSER/TestUser#123@PRODPDB
Output
Connected.

Test INSERT, UPDATE, and DELETE operations on TESTUSER.CUSTOMERS. 

INSERT INTO TESTUSER.CUSTOMERS
(
 CUSTOMER_ID,
 CUSTOMER_NAME,
 EMAIL,
 PHONE,
 CITY,
 STATE,
 CUSTOMER_TYPE,
 STATUS,
 CREDIT_LIMIT,
 CREATED_DATE,
 LAST_LOGIN_DATE
)
VALUES
(
 1001,
 'Audit Test',
 'audit@test.com',
 '9999999999',
 'Delhi',
 'Delhi',
 'TEST',
 'ACTIVE',
 50000,
 SYSDATE,
 SYSDATE
);

UPDATE TESTUSER.CUSTOMERS
SET CREDIT_LIMIT = 75000
WHERE CUSTOMER_ID = 1001;

COMMIT;

DELETE FROM TESTUSER.CUSTOMERS
WHERE CUSTOMER_ID = 1001;

COMMIT;
Output
1 row created.
1 row updated.
Commit complete.
1 row deleted.
Commit complete.

Test INSERT, UPDATE, and DELETE operations on TESTUSER.TEST_DATA. 

INSERT INTO TESTUSER.TEST_DATA
(ID, NAME, AMOUNT)
VALUES
(1001, 'Audit Test', 1000);

UPDATE TESTUSER.TEST_DATA
SET AMOUNT = 2000
WHERE ID = 1001;

COMMIT;

DELETE FROM TESTUSER.TEST_DATA
WHERE ID = 1001;

COMMIT;
Output
1 row created.
1 row updated.
Commit complete.
1 row deleted.
Commit complete.

Step 15: Generate DDL and GRANT/REVOKE Activity

Create and remove temporary objects so that the DDL audit policy can be validated. 

CREATE TABLE TESTUSER.AUDIT_TEST_TABLE
(
  ID NUMBER,
  NAME VARCHAR2(50)
);

ALTER TABLE TESTUSER.AUDIT_TEST_TABLE
ADD CREATED_DATE DATE;

CREATE VIEW TESTUSER.AUDIT_TEST_VIEW
AS
SELECT ID, NAME
FROM TESTUSER.AUDIT_TEST_TABLE;

DROP VIEW TESTUSER.AUDIT_TEST_VIEW;

DROP TABLE TESTUSER.AUDIT_TEST_TABLE;
Output
Table created.
Table altered.
View created.
View dropped.
Table dropped.

Test GRANT and REVOKE activity. 

GRANT SELECT
ON TESTUSER.CUSTOMERS
TO AUDSYS;

REVOKE SELECT
ON TESTUSER.CUSTOMERS
FROM AUDSYS;

GRANT SELECT
ON TESTUSER.CUSTOMERS
TO AUDSYS;
Output
Grant succeeded.
Revoke succeeded.
Grant succeeded.

Step 16: Query the Unified Audit Trail

After generating the test activity, query UNIFIED_AUDIT_TRAIL to verify the records generated by each audit policy. 

SET LINESIZE 250
SET PAGESIZE 100

COLUMN EVENT_TIMESTAMP FORMAT A27
COLUMN DBUSERNAME FORMAT A20
COLUMN ACTION_NAME FORMAT A25
COLUMN OBJECT_SCHEMA FORMAT A20
COLUMN OBJECT_NAME FORMAT A30
COLUMN RETURN_CODE FORMAT 999999
COLUMN SQL_TEXT FORMAT A80 WORD_WRAPPED
COLUMN UNIFIED_AUDIT_POLICY FORMAT A25

SELECT EVENT_TIMESTAMP,
       DBUSERNAME,
       ACTION_NAME,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       UNIFIED_AUDIT_POLICIES,
       SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE UNIFIED_AUDIT_POLICIES LIKE 'DDL_AUDIT_POLICY'
ORDER BY EVENT_TIMESTAMP DESC;
Output
EVENT_TIMESTAMP           DBUSERNAME  ACTION_NAME   OBJECT_SCHEMA  OBJECT_NAME       UNIFIED_AUDIT_POL  SQL_TEXT
------------------------- ----------- ------------ -------------- ---------------- ----------------- -------------------------------
11-08-26 8:05:49.676123   PM TESTUSER  GRANT        TESTUSER       CUSTOMERS        DDL_AUDIT_POLICY   GRANT SELECT ON TESTUSER.CUSTOMERS TO AUDSYS
11-08-26 8:05:31.546572   PM TESTUSER  REVOKE       TESTUSER       CUSTOMERS        DDL_AUDIT_POLICY   REVOKE SELECT ON TESTUSER.CUSTOMERS FROM AUDSYS
11-08-26 8:05:24.740768   PM TESTUSER  GRANT        TESTUSER       CUSTOMERS        DDL_AUDIT_POLICY   GRANT SELECT ON TESTUSER.CUSTOMERS TO AUDSYS
11-08-26 8:04:46.670780   PM TESTUSER  DROP TABLE   TESTUSER       AUDIT_TEST_TABLE DDL_AUDIT_POLICY   DROP TABLE TESTUSER.AUDIT_TEST_TABLE
11-08-26 8:04:36.156308   PM TESTUSER  DROP VIEW    TESTUSER       AUDIT_TEST_VIEW  DDL_AUDIT_POLICY   DROP VIEW TESTUSER.AUDIT_TEST_VIEW
11-08-26 8:04:11.969701   PM TESTUSER  CREATE VIEW  TESTUSER       AUDIT_TEST_VIEW  DDL_AUDIT_POLICY   CREATE VIEW TESTUSER.AUDIT_TEST_VIEW AS SELECT ID, NAME FROM TESTUSER.AUDIT_TEST_TABLE
11-08-26 8:03:44.081444   PM TESTUSER  ALTER TABLE  TESTUSER       AUDIT_TEST_TABLE DDL_AUDIT_POLICY   ALTER TABLE TESTUSER.AUDIT_TEST_TABLE ADD CREATED_DATE DATE
11-08-26 8:03:31.807337   PM TESTUSER  CREATE TABLE TESTUSER       AUDIT_TEST_TABLE DDL_AUDIT_POLICY   CREATE TABLE TESTUSER.AUDIT_TEST_TABLE
8 rows selected.

Step 17: Verify TESTUSER_TESTDATA_DML_AUDIT Records

SELECT EVENT_TIMESTAMP,
       DBUSERNAME,
       ACTION_NAME,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       UNIFIED_AUDIT_POLICIES,
       SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE UNIFIED_AUDIT_POLICIES LIKE 'TESTUSER_TESTDATA_DML_AUDIT'
ORDER BY EVENT_TIMESTAMP DESC;
Output
EVENT_TIMESTAMP           DBUSERNAME ACTION_NAME OBJECT_SCHEMA OBJECT_NAME UNIFIED_AUDIT_POLICIES         SQL_TEXT
------------------------- ---------- ----------- ------------- ----------- ------------------------------ --------------------------------
11-08-26 7:59:16.489056   TESTUSER   DELETE      TESTUSER      TEST_DATA   TESTUSER_TESTDATA_DML_AUDIT  DELETE FROM TESTUSER.TEST_DATA
                                                                                                           WHERE ID = 1001
11-08-26 7:59:04.920982   TESTUSER   UPDATE      TESTUSER      TEST_DATA   TESTUSER_TESTDATA_DML_AUDIT  UPDATE TESTUSER.TEST_DATA
                                                                                                           SET AMOUNT = 2000
                                                                                                           WHERE ID = 1001
11-08-26 7:58:54.637040   TESTUSER   INSERT      TESTUSER      TEST_DATA   TESTUSER_TESTDATA_DML_AUDIT  INSERT INTO TESTUSER.TEST_DATA
                                                                                                           (ID, NAME, AMOUNT)
                                                                                                           VALUES
                                                                                                           (1001, 'Audit Test', 1000)

Step 18: Verify TESTUSER_CUSTOMER_DML_AUDIT Records

SELECT EVENT_TIMESTAMP,
       DBUSERNAME,
       ACTION_NAME,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       UNIFIED_AUDIT_POLICIES,
       SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE UNIFIED_AUDIT_POLICIES LIKE 'TESTUSER_CUSTOMER_DML_AUDIT'
ORDER BY EVENT_TIMESTAMP DESC;
Output
EVENT_TIMESTAMP           DBUSERNAME ACTION_NAME OBJECT_SCHEMA OBJECT_NAME UNIFIED_AUDIT_POLICIES         SQL_TEXT
------------------------- ---------- ----------- ------------- ----------- ------------------------------ --------------------------------
11-08-26 7:58:34.903292   TESTUSER   DELETE      TESTUSER      CUSTOMERS   TESTUSER_CUSTOMER_DML_AUDIT  DELETE FROM TESTUSER.CUSTOMERS
                                                                                                         WHERE CUSTOMER_ID = 1001
11-08-26 7:58:20.777035   TESTUSER   UPDATE      TESTUSER      CUSTOMERS   TESTUSER_CUSTOMER_DML_AUDIT  UPDATE TESTUSER.CUSTOMERS
                                                                                                         SET CREDIT_LIMIT = 75000
                                                                                                         WHERE CUSTOMER_ID = 1001
11-08-26 7:58:14.251117   TESTUSER   INSERT      TESTUSER      CUSTOMERS   TESTUSER_CUSTOMER_DML_AUDIT  INSERT INTO TESTUSER.CUSTOMERS
                                                                                                         (CUSTOMER_ID, CUSTOMER_NAME, EMAIL, ...)
                                                                                                         VALUES (...)
Result: The unified audit trail contains the expected INSERT, UPDATE, DELETE, DDL, GRANT, and REVOKE records generated by TESTUSER.

Understanding Unified Audit Records

The UNIFIED_AUDIT_TRAIL view provides a centralized location for reviewing the audit events generated by enabled Unified Audit Policies. Important columns include the event timestamp, database user, action, object schema, object name, policy name, and SQL text.

Column Purpose
EVENT_TIMESTAMP Timestamp at which the audited event occurred.
DBUSERNAME Database user associated with the operation.
ACTION_NAME Operation performed, such as INSERT, UPDATE, DELETE, CREATE, GRANT, or REVOKE.
OBJECT_SCHEMA Schema containing the affected object.
OBJECT_NAME Object involved in the audited operation.
UNIFIED_AUDIT_POLICIES Audit policy responsible for generating the record.
SQL_TEXT SQL statement associated with the audited event.

Final Unified Auditing Verification

The following queries can be used as a final verification of the Unified Auditing configuration. 

SELECT value
FROM v$option
WHERE parameter = 'Unified Auditing';

SELECT owner,
       table_name,
       interval,
       partitioning_type,
       partition_count,
       def_tablespace_name
FROM dba_part_tables
WHERE owner = 'AUDSYS';

SELECT *
FROM audit_unified_enabled_policies;

SELECT policy_name,
       audit_condition,
       object_name,
       object_type,
       audit_option
FROM audit_unified_policies;

SELECT EVENT_TIMESTAMP,
       DBUSERNAME,
       ACTION_NAME,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       UNIFIED_AUDIT_POLICIES,
       SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
ORDER BY EVENT_TIMESTAMP DESC;

Operational Considerations

  • Perform application and test activity using the intended database user rather than SYS.
  • Monitor the tablespace containing AUDSYS.AUD$UNIFIED.
  • Review audit retention and cleanup requirements before production implementation.
  • Broad audit policies can generate a large number of audit records.
  • Review both successful and failed audit activity according to the organization's security requirements.
  • For Data Guard environments, perform the corresponding Unified Auditing relinking procedure on the DR database.
  • After testing, review whether the test audit policies should remain enabled.
Production Note: Before implementing Unified Auditing in production, validate audit volume, tablespace capacity, audit retention, cleanup requirements, Data Guard considerations, and the scope of every enabled audit policy.

Conclusion

Unified Auditing provides a centralized mechanism for recording database activity through named audit policies and the unified audit trail. In this implementation, Unified Auditing was enabled using the uniaud_on target, verified through V$OPTION, and the unified audit trail was identified as AUDSYS.AUD$UNIFIED.

The audit trail was initially located in SYSAUX and was relocated to the dedicated AUDIT_DATA tablespace using DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION. The default tablespace for the interval partitions of AUD$UNIFIED was subsequently verified as AUDIT_DATA.

DDL and object-level DML policies were then created and enabled. Controlled activity using TESTUSER generated the expected INSERT, UPDATE, DELETE, DDL, GRANT, and REVOKE records, which were verified through UNIFIED_AUDIT_TRAIL.

Key Validation Results:
  • V$OPTION confirmed Unified Auditing = TRUE.
  • AUD$UNIFIED was initially located in SYSAUX and was relocated to AUDIT_DATA.
  • AUDSYS.AUD$UNIFIED was shown with AUDIT_DATA as the default tablespace for its interval partitions.
  • DDL_AUDIT_POLICY was enabled for successful and failed operations with a condition excluding SYS.
  • TESTUSER_CUSTOMER_DML_AUDIT was enabled for INSERT, UPDATE, and DELETE on TESTUSER.CUSTOMERS.
  • TESTUSER_TESTDATA_DML_AUDIT was enabled for INSERT, UPDATE, and DELETE on TESTUSER.TEST_DATA, with successful operations audited.
  • The unified audit trail showed the expected DML, DDL, GRANT, and REVOKE records generated by TESTUSER.
at
Labels: , ,