Skip to main content

Oracle Database Auditing || Unified

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

  • Types of Auditing in Oracle:
    1. Mandatory Auditing : Certain critical actions or users (like SYSDBA) are audited by default without configuration, ensuring that highly sensitive operations are always tracked. Mandatory Auditing refers to the automatic auditing of specific database actions related to highly privileged users and system-level operations. These include:
      1. Logons by privileged users such as those connecting with the SYSDBA, SYSOPER, or similar administrative roles.
      2. Startup and shutdown operations of the Oracle instance.
      3. DDL statements and DML operations affecting the data dictionary and audit infrastructure.
      4. Direct interactions with audit-related system tables, such as the unified audit trail tables.
    2. Standard Auditing : This traditional auditing method lets you track specific SQL statements and privileges. You can audit actions like SELECT, INSERT, UPDATE, DELETE, DDL commands, and user logins by enabling audit policies using the AUDIT SQL statement.
    3. Fine-Grained Auditing (FGA): FGA provides detailed auditing by allowing you to define specific conditions on which to audit access. For example, you can audit access to particular columns or rows based on user-defined predicates, making it useful for monitoring sensitive data access.
    4. Unified Auditing : Introduced in Oracle 12c, Unified Auditing consolidates all audit records from different sources into a single, centralized audit trail. It simplifies audit management and improves performance by unifying standard, fine-grained, and other audit records.
    Below Table will give the comparison view for different types of auditing.
    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)

  • AUDIT_TRAIL Parameter :
    • The AUDIT_TRAIL parameter controls whether and where audit records are stored. It's essential for enabling auditing. Below table will give the view of this parameter value.
    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.

    In the previous articles we have configured the Standard & FGA Auditing.
    Please visit Standard Auditing & FGA Auditing. So, in this article we will configure the Unified Auditing.

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

  • Prerequisites:
    • 30 min downtime.
    • Sufficient space at tablespace & OS level for storing aduit records.

  • Now proceed for enabling unified auditing step by step:

1. First check whether unified audit is enabled or not. Also check AUDIT_TRAIL parameter.

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           = "23-AUG-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> 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> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
-------
FALSE

SQL>
SQL> alter session set container=ORCLPDB;

Session altered.

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> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
--------
FALSE

As the value is FALSE, so unified audit is not enabled.

2. Now we need to enable the unified auditing at binary level. Also set AUDIT_TRAIL parameter.


==> Now set the AUDIT_TRAIL parameter. 

SQL> CREATE PFILE='/home/oracle/pfileorcl.ora' FROM SPFILE;

File created.

SQL> ALTER SYSTEM SET audit_trail=DB, EXTENDED SCOPE=SPFILE;

System altered.


==>Stop database & listener: 

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

[oracle@orcl ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-AUG-2025 13:49:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.oraeasy.com)(PORT=1521)))
The command completed successfully
[oracle@orcl ~]$


==>Follow below for unified auditing: 

[oracle@orcl ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@orcl lib]$
[oracle@orcl lib]$ ls -lrth ins_rdbms*
-rw-r--r--. 1 oracle oinstall 43K Apr 15 13:48 ins_rdbms.mk
[oracle@orcl lib]$
[oracle@orcl lib]$ cp ins_rdbms.mk ins_rdbms_bkp.mk
[oracle@orcl lib]$
[oracle@orcl lib]$ make -f ins_rdbms.mk uniaud_on ioracle
/usr/bin/ar d /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/19.0.0/dbhome_1/bin
cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/;\
/usr/bin/ar r  /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a `/usr/bin/ar t /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a` ;

 - Linking Oracle
rm -f /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orald  -o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/19.0.0/dbhome_1/lib/ -L/u01/app/oracle/product/19.0.0/dbhome_1/lib/stubs/   -Wl,-E /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/app/oracle/product/19.0.0/dbhome_1/lib/nautab.o /u01/app/oracle/product/19.0.0/dbhome_1/lib/naeet.o /u01/app/oracle/product/19.0.0/dbhome_1/lib/naect.o /u01/app/oracle/product/19.0.0/dbhome_1/lib/naedhs.o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/config.o  -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19  -lrt -lplp19 -ldmext -lserver19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 `if [ -f /u01/app/oracle/product/19.0.0/dbhome_1/lib/libavserver19.a ] ; then echo "-lavserver19" ; else echo "-lavstub19"; fi` `if [ -f /u01/app/oracle/product/19.0.0/dbhome_1/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19  -lrt -lplp19 -ljavavm19 -lserver19  -lwwg  `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19   -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if /usr/bin/ar tv /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/app/oracle/product/19.0.0/dbhome_1/ctx/lib/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19  -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore  -lippcp -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19  -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19  -ledtn19 -laio -lons  -lmql1 -lipc1    -lfthread19    `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/19.0.0/dbhome_1/lib -lm    `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/19.0.0/dbhome_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle
mv /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle
chmod 6751 /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle
(if [ ! -f /u01/app/oracle/product/19.0.0/dbhome_1/bin/crsd.bin ]; then \
    getcrshome="/u01/app/oracle/product/19.0.0/dbhome_1/srvm/admin/getcrshome" ; \
    if [ -f "$getcrshome" ]; then \
        crshome="`$getcrshome`"; \
        if [ -n "$crshome" ]; then \
            if [ $crshome != /u01/app/oracle/product/19.0.0/dbhome_1 ]; then \
                oracle="/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle"; \
                $crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
            fi \
        fi \
    fi \
fi\
);
[oracle@orcl lib]$


==>Start database & listener: 

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> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
--------
TRUE

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>
SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
--------
TRUE

SQL> exit

[oracle@orcl ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-AUG-2025 14:00:05

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

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.oraeasy.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.oraeasy.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-AUG-2025 14:00:05
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.oraeasy.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@orcl ~]$
3. Now we need to assign a tablespace for storing the audit data by using DBMS_AUDIT_MGMT package. By default it is set to SYSAUX tablespace. 

SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN owner FORMAT A15
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN interval FORMAT A30
SQL> SELECT owner,
            table_name,
            interval,
            partitioning_type,
            partition_count,
            def_tablespace_name
     FROM dba_part_tables
     WHERE owner = 'AUDSYS';

OWNER           TABLE_NAME           INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
--------------- -------------------- ------------------------------ --------- --------------- ------------------------------
AUDSYS          AUD$UNIFIED          INTERVAL '1' MONTH             RANGE             1048575 SYSAUX

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>
BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
        audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        audit_trail_location_value => 'AUDIT_DATA'
    );
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT owner,
            table_name,
            interval,
            partitioning_type,
            partition_count,
            def_tablespace_name
     FROM dba_part_tables
     WHERE owner = 'AUDSYS';
     
OWNER           TABLE_NAME           INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
--------------- -------------------- ------------------------------ --------- --------------- ------------------------------
AUDSYS          AUD$UNIFIED          INTERVAL '1' MONTH             RANGE             1048575 AUDIT_DATA
4. Now we need to create the audit policy. Here we will enable DDL auditing.


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> 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;
    
Audit policy created.
5. Enable the policy.

SQL> AUDIT POLICY ddl_audit_policy;

Audit succeeded.
6. Now check the enabled audit policy.

SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN policy_name FORMAT A25
SQL> COLUMN entity_name FORMAT A15
SQL> COLUMN success FORMAT A10
SQL> COLUMN failure FORMAT A10
SQL> SELECT *
     FROM audit_unified_enabled_policies;

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

SQL>
SQL> COLUMN audit_condition FORMAT A50
SQL> COLUMN object_name FORMAT A20
SQL> COLUMN audit_option FORMAT A30
SQL> SELECT policy_name,
            audit_condition,
            object_name,
            object_type,
            audit_option
     FROM audit_unified_policies
     WHERE policy_name = 'DDL_AUDIT_POLICY';
   
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

SQL>

ORA_SECURECONFIG & ORA_LOGON_FAILURES are Oracle defined policies and enabled by default.

7. Now do some DDL operations.

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.


==>With TESTAUDIT user: 

SQL> SHOW USER
USER is "TESTAUDIT"

SQL> DEF
DEFINE _DATE           = "23-AUG-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> CREATE TABLE company (
      emp_id   INT,
      name     VARCHAR(255),
      company  VARCHAR(255)
  );

Table created.

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

Table altered.

SQL> DROP TABLE company;

Table dropped.

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

Table created.
8. Now check the captured Audit records with SYS user.

SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN event_timestamp FORMAT A40
SQL> COLUMN action_name FORMAT A15
SQL> COLUMN sql_text FORMAT A60
SQL> COLUMN dbusername FORMAT A20
SQL> SELECT event_timestamp,
            dbusername,
            action_name,
            sql_text
     FROM unified_audit_trail
     ORDER BY event_timestamp DESC;

EVENT_TIMESTAMP                          DBUSERNAME           ACTION_NAME     SQL_TEXT
---------------------------------------- -------------------- --------------- ------------------------------------------------------------
23-AUG-25 07.25.04.140615 PM             TESTAUDIT            CREATE TABLE    CREATE TABLE COMPANY (EMP_ID INT,NAME VARCHAR(255), COMPANY
                                                                              VARCHAR(255))

23-AUG-25 07.24.55.252860 PM             TESTAUDIT            DROP TABLE      drop table COMPANY
23-AUG-25 07.24.28.117447 PM             TESTAUDIT            ALTER TABLE     alter table COMPANY add city varchar2(20)
23-AUG-25 07.24.05.784920 PM             TESTAUDIT            CREATE TABLE    CREATE TABLE COMPANY (EMP_ID INT,NAME VARCHAR(255), COMPANY
                                                                              VARCHAR(255))

23-AUG-25 07.19.11.847290 PM             SYS                  GRANT           grant connect,resource to testaudit
23-AUG-25 07.18.58.518906 PM             SYS                  CREATE USER      create user testaudit identified by *
9. In similar way we can enable DML audit also.

SQL> CREATE AUDIT POLICY company_dml_audit
  ACTIONS INSERT, UPDATE, DELETE ON testaudit.company;

Audit policy created.

SQL> AUDIT POLICY company_dml_audit;

Audit succeeded.


==> Now check the DML audit policy. 

SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN policy_name FORMAT A25
SQL> COLUMN entity_name FORMAT A15
SQL> COLUMN success FORMAT A10
SQL> COLUMN failure FORMAT A10
SQL> SELECT *
     FROM audit_unified_enabled_policies;

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
COMPANY_DML_AUDIT         BY USER         ALL USERS       USER    YES        YES

SQL>
SQL> COLUMN audit_condition FORMAT A50
SQL> COLUMN object_name FORMAT A20
SQL> COLUMN audit_option FORMAT A30
SQL> SELECT policy_name,
            audit_condition,
            object_name,
            object_type,
            audit_option
     FROM audit_unified_policies
     WHERE policy_name = 'COMPANY_DML_AUDIT';

POLICY_NAME               AUDIT_CONDITION    OBJECT_NAME          OBJECT_TYPE             AUDIT_OPTION
------------------------- ------------------ -------------------- ----------------------- ---------------
COMPANY_DML_AUDIT         NONE               NONE                 NONE                    INSERT
COMPANY_DML_AUDIT         NONE               NONE                 NONE                    UPDATE
COMPANY_DML_AUDIT         NONE               COMPANY              TABLE                   DELETE
10. Now take a fresh login with testaudit and do DML.

SQL> SHOW USER
USER is "TESTAUDIT"

SQL> DEF
DEFINE _DATE           = "23-AUG-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> INSERT INTO company VALUES (101, 'Yash', 'WIPRO');

1 row created.

SQL> UPDATE company SET name = 'RAJ' WHERE emp_id = 101;

1 row updated.

SQL> DELETE FROM company;

1 row deleted.

SQL> COMMIT;

Commit complete.
11. Now check the captured Audit records with SYS user.

SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN event_timestamp FORMAT A40
SQL> COLUMN action_name FORMAT A15
SQL> COLUMN sql_text FORMAT A60
SQL> COLUMN dbusername FORMAT A20
SQL> SELECT event_timestamp,
            dbusername,
            action_name,
            sql_text
     FROM unified_audit_trail
     ORDER BY event_timestamp DESC;

EVENT_TIMESTAMP                          DBUSERNAME           ACTION_NAME     SQL_TEXT
---------------------------------------- -------------------- --------------- ------------------------------------------------------------
23-AUG-25 07.47.34.662020 PM             TESTAUDIT            DELETE          delete from COMPANY
23-AUG-25 07.47.15.582965 PM             TESTAUDIT            UPDATE          update COMPANY set NAME='RAJ' where EMP_ID=101
23-AUG-25 07.46.44.250120 PM             TESTAUDIT            INSERT           INSERT INTO COMPANY VALUES (101,'Yash','WIPRO')
23-AUG-25 07.45.12.454288 PM             SYS                  AUDIT           AUDIT POLICY company_dml_audit
23-AUG-25 07.45.02.679294 PM             SYS                  CREATE AUDIT PO CREATE AUDIT POLICY company_dml_audit
                                                              LICY              ACTIONS INSERT, UPDATE, DELETE ON testau

23-AUG-25 07.42.24.140615 PM             TESTAUDIT            CREATE TABLE    CREATE TABLE COMPANY (EMP_ID INT,NAME VARCHAR(255), COMPANY
                                                                              VARCHAR(255))

23-AUG-25 07.41.54.033706 PM             SYS                  CREATE AUDIT PO CREATE AUDIT POLICY company_dml_audit
                                                              LICY              ACTIONS INSERT, UPDATE, DELETE ON testau

23-AUG-25 07.24.55.252860 PM             TESTAUDIT            DROP TABLE      drop table COMPANY
23-AUG-25 07.24.28.117447 PM             TESTAUDIT            ALTER TABLE     alter table COMPANY add city varchar2(20)
23-AUG-25 07.24.05.784920 PM             TESTAUDIT            CREATE TABLE    CREATE TABLE COMPANY (EMP_ID INT,NAME VARCHAR(255), COMPANY
                                                                              VARCHAR(255))

23-AUG-25 07.19.11.847290 PM             SYS                  GRANT           grant connect,resource to testaudit
23-AUG-25 07.18.58.518906 PM             SYS                  CREATE USER      create user testaudit identified by *
12. If we need to disable or drop any policy, we can do using below. 


==> To disable. 

SQL> NOAUDIT POLICY COMPANY_DML_AUDIT BY TEST;

Noaudit succeeded.

SQL> NOAUDIT POLICY COMPANY_DML_AUDIT;

Noaudit succeeded.


==> To drop. 

SQL> DROP AUDIT POLICY COMPANY_DML_AUDIT;

Audit Policy dropped.

SQL>

13. Now if we need to disable the unified auditing at binary level, then follow below steps.


==>Stop database & listener: 

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

[oracle@orcl ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-AUG-2025 12:45:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.oraeasy.com)(PORT=1521)))
The command completed successfully
[oracle@orcl ~]$


==>Follow below to disable unified auditing: 

[oracle@orcl ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@orcl lib]$
[oracle@orcl lib]$ ls -lrth ins_rdbms*
-rw-r--r--. 1 oracle oinstall 43K Apr 15 13:48 ins_rdbms.mk
-rw-r--r--. 1 oracle oinstall 43K Aug 23 13:52 ins_rdbms_bkp.mk
[oracle@orcl lib]$
[oracle@orcl lib]$ make -f ins_rdbms.mk uniaud_off ioracle
/usr/bin/ar d /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a kzaiang.o
/usr/bin/ar cr /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/kzanang.o
chmod 755 /u01/app/oracle/product/19.0.0/dbhome_1/bin
cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/;\
/usr/bin/ar r  /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a `/usr/bin/ar t /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a` ;

 - Linking Oracle
rm -f /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orald  -o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/19.0.0/dbhome_1/lib/ -L/u01/app/oracle/product/19.0.0/dbhome_1/lib/stubs/   -Wl,-E /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/app/oracle/product/19.0.0/dbhome_1/lib/nautab.o /u01/app/oracle/product/19.0.0/dbhome_1/lib/naeet.o /u01/app/oracle/product/19.0.0/dbhome_1/lib/naect.o /u01/app/oracle/product/19.0.0/dbhome_1/lib/naedhs.o /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/config.o  -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19  -lrt -lplp19 -ldmext -lserver19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 `if [ -f /u01/app/oracle/product/19.0.0/dbhome_1/lib/libavserver19.a ] ; then echo "-lavserver19" ; else echo "-lavstub19"; fi` `if [ -f /u01/app/oracle/product/19.0.0/dbhome_1/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19  -lrt -lplp19 -ljavavm19 -lserver19  -lwwg  `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19   -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if /usr/bin/ar tv /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/app/oracle/product/19.0.0/dbhome_1/ctx/lib/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19  -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore  -lippcp -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19  -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19  -ledtn19 -laio -lons  -lmql1 -lipc1    -lfthread19    `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/19.0.0/dbhome_1/lib -lm    `cat /u01/app/oracle/product/19.0.0/dbhome_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/19.0.0/dbhome_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle
mv /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle
chmod 6751 /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle
(if [ ! -f /u01/app/oracle/product/19.0.0/dbhome_1/bin/crsd.bin ]; then \
    getcrshome="/u01/app/oracle/product/19.0.0/dbhome_1/srvm/admin/getcrshome" ; \
    if [ -f "$getcrshome" ]; then \
        crshome="`$getcrshome`"; \
        if [ -n "$crshome" ]; then \
            if [ $crshome != /u01/app/oracle/product/19.0.0/dbhome_1 ]; then \
                oracle="/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle"; \
                $crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
            fi \
        fi \
    fi \
fi\
);
[oracle@orcl lib]$


==>Start database & listener: 

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> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
--------
FALSE

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> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
--------
FALSE

Now If we need to purge the audit data. We can follow the below steps and by using DBMS_AUDIT_MGMT package.

1. Now first check the current audit records.


SQL> SELECT COUNT(*) FROM unified_audit_trail;

  COUNT(*)
----------
     11818

SQL> SELECT COUNT(*) FROM unified_audit_trail 
     WHERE event_timestamp < SYSTIMESTAMP - 5;

  COUNT(*)
----------
      4113
2. Now set LAST_ARCHIVE_TIMESTAMP parameter and check the same.

SQL> BEGIN
     DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
         audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
         last_archive_time => SYSTIMESTAMP - 5
     );
    END;
    /

PL/SQL procedure successfully completed.

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 14-AUG-25 07.47.58.000000 PM +00:00       1593504508 2C3D6BACF692345BE0633350A8C050ED
STANDARD AUDIT TRAIL            0 03-AUG-25 07.46.26.000000 PM +00:00       1593504508 2C3D6BACF692345BE0633350A8C050ED
UNIFIED AUDIT TRAIL             0 25-AUG-25 10.52.41.000000 AM +00:00       1593504508 2C3D6BACF692345BE0633350A8C050ED
3. Now purge the audit records older than 5 days. 


SQL> BEGIN
      DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
            audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
            use_last_arch_timestamp => TRUE
        );
     END;
     /

PL/SQL procedure successfully completed.

SQL>
4. Now again verify the current audit records.


SQL> SELECT COUNT(*) FROM unified_audit_trail;

  COUNT(*)
----------
      7710


SQL> SELECT COUNT(*) FROM unified_audit_trail 
     WHERE event_timestamp < SYSTIMESTAMP - 5;

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

SQL>
#. If you encountered that AUDIT Data is still getting stored in SYSAUX instead of AUDIT_DATA tablespace. Then check the partition interval for AUD$UNIFIED table. By default it is set for month and we can change it by using DBMS_AUDIT_MGMT package.


==> To check the current partition interval. 

SQL> SELECT owner,
       table_name,
       interval,
       partitioning_type,
       partition_count,
       def_tablespace_name
FROM   dba_part_tables
WHERE  table_name = 'AUD$UNIFIED';

OWNER           TABLE_NAME           INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
--------------- -------------------- ------------------------------ --------- --------------- ------------------------------
AUDSYS          AUD$UNIFIED          INTERVAL '1' MONTH             RANGE             1048575 AUDIT_DATA


==> To change the interval for 1 day. 

SQL> BEGIN
    DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
        interval_number      => 1,
        interval_frequency   => 'DAY'
    );
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT owner,
       table_name,
       interval,
       partitioning_type,
       partition_count,
       def_tablespace_name
FROM   dba_part_tables
WHERE  table_name = 'AUD$UNIFIED';

OWNER           TABLE_NAME           INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
--------------- -------------------- ------------------------------ --------- --------------- ------------------------------
AUDSYS          AUD$UNIFIED          NUMTODSINTERVAL(1, 'DAY')      RANGE             1048575 AUDIT_DATA
#. Now if you have huge records in AUDIT data then querying unified_audit_trail view will be very tough. Instead of unified_audit_trail view, we can query the exact partition of table AUD$UNIFIED which meets our requirement. Follow below.


==> To check the available partition. 

SQL> COLUMN table_name FORMAT A15
SQL> COLUMN partition_name FORMAT A15
SQL> COLUMN high_value FORMAT A15
SQL> SELECT table_name,
            partition_name,
            partition_position,
            high_value,
            num_rows,
            tablespace_name
     FROM dba_tab_partitions
     WHERE table_owner = 'AUDSYS'
     AND table_name = 'AUD$UNIFIED'
     ORDER BY partition_position;
     
TABLE_NAME      PARTITION_NAME  PARTITION_POSITION HIGH_VALUE                       NUM_ROWS TABLESPACE_NAME
--------------- --------------- ------------------ ------------------------------ ---------- ------------------------------
AUD$UNIFIED     SYS_P668                         1 TIMESTAMP' 2025-09-01 00:00:00       4428 AUDIT_DATA


==> To query the particular partition. 


SQL>  SELECT count(*) FROM AUDSYS.AUD$UNIFIED PARTITION  (SYS_P668);

  COUNT(*)
----------
      7714
Please note that the COLUMN names is different in AUD$UNIFIED table comapre to unified_audit_trail view. You need to do some R&D on COLUMN names to find your required data.


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

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

Follow Us

Location: Noida, Uttar Pradesh, India

Comments

Post a Comment

↑