Oracle DBMS_AUDIT_MGMT
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Provides utilities that enable audit administrators to manage the audit trail. In a mixed mode environment, these audit trails comprise the database, operating system (OS), and XML audit trails. In a unified auditing environment, this comprises the unified audit trail.
AUTHID DEFINER
Constants
Name Data Type Value
Audit Trail types
AUDIT_TRAIL_AUD_STD NUMBER 1
AUDIT_TRAIL_FGA_STD NUMBER 2
AUDIT_TRAIL_DB_STD (both AUD + FGA) NUMBER 3
AUDIT_TRAIL_OS NUMBER 4
AUDIT_TRAIL_XML NUMBER 8
AUDIT_TRAIL_FILES (both OS & XML) NUMBER 12
AUDIT_TRAIL_ALL NUMBER 15
OS Audit File Configuration parameters
OS_FILE_MAX_SIZE NUMBER 16
OS_FILE_MAX_AGE NUMBER 17
Miscellaneous
CLEAN_UP_INTERVAL NUMBER 21
DB_AUDIT_TABLEPSACE NUMBER 22
DB_DELETE_BATCH_SIZE NUMBER 23
TRACE_LEVEL NUMBER 24
AUD_TAB_MOVEMENT_FLAG NUMBER 25
FILE_DELETE_BATCH_SIZE NUMBER 26
Purge Job Status values
PURGE_JOB_ENABLE NUMBER 31
PURGE_JOB_DISABLE NUMBER 32
NG Audit Trail write mode configuration
AUDIT_TRAIL_WRITE_MODE NUMBER 33
Write Mode values
AUDIT_TRAIL_QUEUED_WRITE NUMBER 1
AUDIT_TRAIL_IMMEDIATE_WRITE NUMBER 2
Trace Level values
TRACE_LEVEL_DEBUG PLS_INTEGER 1
TRACE_LEVEL_ERROR PLS_INTEGER 2
Unified Audit Trail
AUDIT_TRAIL_UNIFIED NUMBER 51
Container Values
CONTAINER_CURRENT NUMBER 1
CONTAINER_ALL NUMBER 2
Flush Types
FLUSH_CURRENT_INSTANCE NUMBER 1
FLUSH_ALL_INSTANCES NUMBER 2
Partition Interview
DEFAULT_INTERVAL_NUMBER NUMBER 1
DEFAULT_INTERVAL_FREQUENCY VARCHAR2(5) 'MONTH'
Dependencies
ALL_TAB_COLS DBA_USERS DUAL
AMGT$DATAPUMP DBMS_ASSERT GET_AUD_PDB_LIST
AUD_PDB_LIST DBMS_AUDIT_MGMT_LIB GV$INSTANCE
DAM_CLEANUP_JOBS$ DBMS_INTERNAL_LOGSTDBY OBJ$
DAM_CONFIG_PARAM$ DBMS_LOCK PLITBLM
DAM_LAST_ARCH_TS$ DBMS_PDB_EXEC_SQL REGISTRY$
DBA_AUDIT_MGMT_CONFIG_PARAMS DBMS_SCHEDULER V$CONTAINERS
DBA_FREE_SPACE DBMS_SQL V$DATABASE
DBA_TABLES DBMS_STANDARD V$INSTANCE
DBA_TABLESPACES DBMS_STATS V$OPTION
DBA_TAB_PARTITIONS DBMS_UTILITY V$VERSION
Documented Yes
Exceptions
Error Code Reason
ORA-46273 DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-55906 Secure file log [id: 0 name: ORA$AUDIT_NEXTGEN_LOG] does not exist
First Available 11.1.0.7
Security Model Owned by AUDSYS with EXECUTE granted to the AUDIT_ADMIN and EXECUTE_CATALOG_ROLE roles
Source {ORACLE_HOME}/rdbms/admin/dbmsamgt.sql
Subprograms
 
ALTER_PARTITION_INTERVAL
Alters the interval of partitioned table AUDSYS.AUD$UNIFIED dbms_audit_mgmt.alter_partition_interval(
interval_number    IN PLS_INTEGER := DEFAULT_INTERVAL_NUMBER,
interval_frequency IN VARCHAR2    := DEFAULT_INTERVAL_FREQUENCY);
exec dbms_audit_mgmt.alter_partition_interval(dbms_audit_mgmt.default_interval_frequency, 'DAY');
 
CLEAN_AUDIT_TRAIL
Deletes entries in audit trail according to the timestamp set in set_last_archive_timestamp dbms_audit_mgmt.clean_audit_trail(
audit_trail_type        IN PLS_INTEGER,
use_last_arch_timestamp IN BOOLEAN     := TRUE,
container               IN PLS_INTEGER := CONTAINER_CURRENT,
database_id             IN NUMBER      := NULL,
container_guid          IN VARCHAR2    := NULL);
See IS_CLEANUP_INITIALIZED Demo Below
 
CLEAR_AUDIT_TRAIL_PROPERTY
Clears an audit trail property dbms_audit_mgmt.clear_audit_trail_property(
audit_trail_type     IN PLS_INTEGER,
audit_trail_property IN PLS_INTEGER,
use_default_values   IN BOOLEAN := FALSE);
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_os;
 atp NUMBER := dbms_audit_mgmt.os_file_max_age;
BEGIN
  dbms_audit_mgmt.set_audit_trail_property(att, atp, 30);
  dbms_audit_mgmt.clear_audit_trail_property(att, atp, TRUE);
END;
/
 
CLEAR_LAST_ARCHIVE_TIMESTAMP
Deletes the timestamp set by set_last_archive_timestamp dbms_audit_mgmt.clear_last_archive_timestamp(
audit_trail_type    IN PLS_INTEGER,
rac_instance_number IN PLS_INTEGER := NULL,
container           IN PLS_INTEGER,
database_id         IN NUMBER,
container_guid      IN RAW);
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_os;
BEGIN
  dbms_audit_mgmt.clear_last_archive_timestamp(att, NULL, dbms_audit_mgmt.container_all);
  dbms_audit_mgmt.clear_last_archive_timestamp(att, NULL, dbms_audit_mgmt.container_all);
  dbms_audit_mgmt.clear_last_archive_timestamp(att, NULL, dbms_audit_mgmt.container_all);
END;
/
 
CREATE_PURGE_JOB
Creates a purge job for an audit trail dbms_audit_mgmt.create_purge_job(
audit_trail_type           IN PLS_INTEGER,
audit_trail_purge_interval IN PLS_INTEGER,
audit_trail_purge_name     IN VARCHAR2,
use_last_arch_timestamp    IN BOOLEAN     := TRUE,
container                  IN PLS_INTEGER := CONTAINER_CURRENT);
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
 pje NUMBER := dbms_audit_mgmt.purge_job_enable;
BEGIN
  dbms_audit_mgmt.create_purge_job(att, 48, 'UW_PURGE', TRUE);
  dbms_audit_mgmt.set_purge_job_interval('UW_PURGE', 48);
  dbms_audit_mgmt.set_purge_job_status('UW_PURGE', pje, dbms_audit_mgmt.container_all);
  dbms_audit_mgmt.drop_purge_job('UW_PURGE');
END;
/
 
DEINIT_CLEANUP
De-Initialize DBMS_AUDIT_MGMT dbms_audit_mgmt.deinit_cleanup(
audit_trail_type IN PLS_INTEGER,
container        IN PLS_INTEGER  := CONTAINER_CURRENT);
See IS_CLEANUP_INITIALIZED Demos Below
 
DROP_OLD_UNIFIED_AUDIT_TABLES
Drops the given Old Unified Audit (CLI based) tables dbms_audit_mgmt.drop_old_unified_audit_tables(container_guid IN VARCHAR2);
SELECT con_id, name, guid
FROM v$pdbs
ORDER BY 1;

exec dbms_audit_mgmt.drop_old_unified_audit_tables('4C690F3954EC4B2FBECFAA0CFA1BD955');
 
DROP_PURGE_JOB
Drops the purge job for an audit trail dbms_audit_mgmt.drop_purge_job(audit_trail_purge_name IN VARCHAR2);
See CREATE_PURGE_JOB Demo Above
 
FLUSH_UNIFIED_AUDIT_TRAIL
Writes the unified audit trail records in the SGA queue to disk dbms_audit_mgmt.flush_unified_audit_trail(
flush_type IN PLS_INTEGER := FLUSH_CURRENT_INSTANCE,
container  IN PLS_INTEGER := CONTAINER_CURRENT);
DECLARE
 flt PLS_INTEGER := dbms_audit_mgmt.flush_current_instance;
 con PLS_INTEGER := dbms_audit_mgmt.container_current;
BEGIN
  dbms_audit_mgmt.flush_unified_audit_trail(flt, con);
END;
/
 
GET_AUDIT_COMMIT_DELAY
GETs the audit commit delay set in the database. The default is 15. dbms_audit_mgmt.get_audit_commit_delay RETURN PLS_INTEGER;
SELECT dbms_audit_mgmt.get_audit_commit_delay
FROM dual;
 
GET_AUDIT_TRAIL_PROPERTY_VALUE
Retrieves the value of the property set by set_audit_trail_property dbms_audit_mgmt.get_audit_trail_property_value(
audit_trail_type     IN PLS_INTEGER,
audit_trail_property IN PLS_INTEGER)
RETURN NUMBER;
DECLARE
 att PLS_INTEGER := dbms_audit_mgmt.audit_trail_os;
 atp PLS_INTEGER := dbms_audit_mgmt.os_file_max_age;
 n   NUMBER;
BEGIN
 n := dbms_audit_mgmt.get_audit_trail_property_value(att, atp);
END;
/
 
GET_CLI_PART_ORANUM
Returns the ORACLE NUMBER corresponding to the HIGH_VALUE of CLI Partition dbms_audit_mgmt.get_cli_part_oranum(partname IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_audit_mgmt.get_cli_part_oranum('AUD_UNIFIED_P0')
FROM dual;
 
GET_LAST_ARCHIVE_TIMESTAMP
Returns the timestamp set by set_last_archive_timestamp for the current instance dbms_audit_mgmt.get_last_archive_timestamp(audit_trail_type IN PLS_INTEGER) RETURN TIMESTAMP;
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
 lat TIMESTAMP := TO_TIMESTAMP('15-MAR-17 14:42:42.00','DD-MON-YYYY HH24:MI:SS.FF');
BEGIN
  dbms_audit_mgmt.init_cleanup(att, 24, dbms_audit_mgmt.container_all);
  dbms_audit_mgmt.set_last_archive_timestamp(att, lat, NULL);
END;
/

DECLARE
 att PLS_INTEGER := dbms_audit_mgmt.audit_trail_xml;
 tsp TIMESTAMP;
BEGIN
  tsp := dbms_audit_mgmt.get_last_archive_timestamp(att);
END;
/

DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
BEGIN
  IF dbms_audit_mgmt.is_cleanup_initialized(att, dbms_audit_mgmt.container_all) THEN
    dbms_output.put_line('Cleanup Is Initialized');
    dbms_audit_mgmt.deinit_cleanup(att, dbms_audit_mgmt.container_all);
  END IF;
END;
/
 
GET_PART_HIGHVAL_AS_CHAR
Returns the aud$unified table partition HIGH_VALUE as a VARCHAR2. dbms_audit_mgmt.get_part_highval_as_char(partname IN VARCHAR2)
RETURN VARCHAR2;
SQL> select partition_name from dba_tab_partitions where table_name = 'AUD$UNIFIED';

PARTITION_NAME
------------------------------
AUD_UNIFIED_P0
SYS_P185
SYS_P687

SQL> SELECT dbms_audit_mgmt.get_part_highval_as_char('AUD_UNIFIED_P0')
  2  FROM dual;

DBMS_AUDIT_MGMT.GET_PART_HIGHVAL_AS_CHAR('AUD_UNIFIED_P0')
----------------------------------------------------------
2014-07-01 00:00:00

SQL> SELECT dbms_audit_mgmt.get_part_highval_as_char('SYS_P687')
  2  FROM dual;

DBMS_AUDIT_MGMT.GET_PART_HIGHVAL_AS_CHAR('AUD_UNIFIED_P0')
----------------------------------------------------------
2019-01-01 00:00:00
 
INIT_CLEANUP
Initializes DBMS_AUDIT_MGMT dbms_audit_mgmt.init_cleanup(
audit_trail_type         IN PLS_INTEGER,
default_cleanup_interval IN PLS_INTEGER,
container                IN PLS_INTEGER := CONTAINER_CURRENT);
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
BEGIN
  dbms_audit_mgmt.init_cleanup(att, 24, dbms_audit_mgmt.container_all);
END;
/
 
IS_CLEANUP_INITIALIZED
Checks if Audit Cleanup is initialized for the audit trail type

Overload 1
dbms_audit_mgmt.is_cleanup_initialized(
audit_trail_type IN PLS_INTEGER,
container        IN PLS_INTEGER)
RETURN BOOLEAN;
set serveroutput on

DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
BEGIN
  IF dbms_audit_mgmt.is_cleanup_initialized(att, dbms_audit_mgmt.container_all) THEN
    dbms_output.put_line('Cleanup Is Initialized');
    dbms_audit_mgmt.set_audit_trail_location(att, 'UWDATA');
    dbms_audit_mgmt.deinit_cleanup(att, dbms_audit_mgmt.container_all);
  ELSE
    dbms_output.put_line('Cleanup Was Not Initialized');
    dbms_audit_mgmt.init_cleanup(att, 24, dbms_audit_mgmt.container_all);
    dbms_audit_mgmt.clean_audit_trail(att, TRUE, dbms_audit_mgmt.container_all);
  END IF;
END;
/
Overload 2 dbms_audit_mgmt.is_cleanup_initialized(
audit_trail_type   IN PLS_INTEGER,
container          IN PLS_INTEGER,
uninitialized_pdbs IN dbms_sql.varchar2s)
RETURN BOOLEAN;
DECLARE
 att    NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
 uipdbs dbms_sql.varchar2s;
BEGIN
  uipdbs(1) := 'ORADEV';
  uipdbs(2) := 'ORATEST';
  IF dbms_audit_mgmt.is_cleanup_initialized(att, 2, uipdbs) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
IS_CLEANUP_INITIALIZED2
Checks if Audit Cleanup is initialized for the audit trail type and returns VARCHAR2 type dbms_audit_mgmt.is_cleanup_initialized2(
audit_trail_type IN PLS_INTEGER,
container        IN PLS_INTEGER := CONTAINER_CURRENT)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
 ret VARCHAR2(5);
BEGIN
  ret := dbms_audit_mgmt.is_cleanup_initialized2(att);
  dbms_output.put_line(ret);
END;
/
 
IS_DROPPABLE_PARTITION
If the identified aud$unified partition is droppable returns 1; otherwise 0 dbms_audit_mgmt.is_droppable_partition(
partname IN VARCHAR2,
lat      IN TIMESTAMP)
RETURN NUMBER;
SELECT dbms_audit_mgmt.is_droppable_partition('SYS_P185', SYSTIMESTAMP-16)
FROM dual;
 
LOAD_UNIFIED_AUDIT_FILES
Loads all spillover audit files to tables. container is the PDB Container to execute in dbms_audit_mgmt.load_unified_audit_files(
container IN PLS_INTEGER DEFAULT := CONTAINER_CURRENT);
exec dbms_audit_mgmt.load_unified_audit_files(dbms_audit_mgmt.container_current);
 
MOVE_DBAUDIT_TABLES
Moves DB audit tables to specified tablespace dbms_audit_mgmt.move_dbaudit_tables(audit_trail_tbs IN VARCHAR2 DEFAULT 'SYSAUX');
CREATE TABLESPACE audit_data
DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\ORABASE\AUDDATA.DBF' SIZE 100M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

exec dbms_audit_mgmt.move_dbaudit_tables('audit_data');
 
SET_AUDIT_TRAIL_LOCATION
Sets the destination for an audit trail
Audit trail types are AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_FGA_STD, and AUDIT_TRAIL_DB_STD
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type           IN PLS_INTEGER,
audit_trail_location_value IN VARCHAR2);   -- tablespace name
See IS_CLEANUP_INITIALIZED Demo Above
 
SET_AUDIT_TRAIL_PROPERTY
Sets an property of an audit trail dbms_audit_mgmt.set_audit_trail_property(
audit_trail_type           IN PLS_INTEGER,
audit_trail_property       IN PLS_INTEGER,
audit_trail_property_value IN PLS_INTEGER);
See CLEAR_AUDIT_TRAIL_PROPERTY Demo Above
 
SET_DEBUG_LEVEL
Sets the debug level for tracing dbms_audit_mgmt.set_debug_level(debug_level IN PLS_INTEGER := TRACE_LEVEL_ERROR);
exec dbms_audit_mgmt.set_debug_level(dbms_audit_mgmt.trace_level_error);
 
SET_LAST_ARCHIVE_TIMESTAMP
Sets the timestamp when the last audit records were archived dbms_audit_mgmt.set_last_archive_timestamp(
audit_trail_type    IN PLS_INTEGER,
last_archive_time   IN TIMESTAMP,
rac_instance_number IN PLS_INTEGER := NULL,
container           IN PLS_INTEGER := NULL,
database_id         IN NUMBER      := NULL,
container_guid      IN VARCHAR2    := NULL);
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
 lat TIMESTAMP := TO_TIMESTAMP('14-OCT-14 14:42:42.00','DD-MON-YYYY HH24:MI:SS.FF');
BEGIN
  dbms_audit_mgmt.set_last_archive_timestamp(att, lat, NULL);
END;
/
 
SET_PURGE_JOB_INTERVAL
Set the interval of the purge job dbms_audit_mgmt.set_purge_job_interval(
audit_trail_purge_name     IN VARCHAR2,
audit_trail_interval_value IN PLS_INTEGER);
See CREATE_PURGE_JOB Demo Above
 
SET_PURGE_JOB_STATUS
Set the status of the purge job dbms_audit_mgmt.set_purge_job_status(
audit_trail_purge_name   IN VARCHAR2,
audit_trail_status_value IN PLS_INTEGER);
See CREATE_PURGE_JOB Demo Above
 
TRANSFER_UNIFIED_AUDIT_RECORDS
Transfers audit records from the common logging infrastructure (CLI) swap table to the AUDSYS.AUD$UNIFIED relational table dbms_audit_mgmt.transfer_unified_audit_records(
container_guid IN VARCHAR2 DEFAULT CONTAINER_CURRENT);
exec dbms_audit_mgmt.transfer_unified_audit_records;

Related Topics
Auditing
Built-in Functions
Built-in Packages
DBMS_AUDIT_UTIL
DBMS_AUDIT_MGMT_LSBY
DBMS_FGA
DBMS_SQL
Fine Grained Auditing
Unified Audit Policies
What's New In 18c
What's New In 19c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx