Oracle DBMS_AUDIT_MGMT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose The following is contained in the file header:
The package can be logically split into two packages - one intended for use by AV collectors and one by Audit Administrators. The first 3 procedures will be mainly used by the Collectors and the rest must be executed by Audit Admins. Alternately, wrapper packages can be written to achieve this Seperation of Duty.
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
Dependencies
ALL_TAB_COLS DBMS_ASSERT DUAL
AMGT$DATAPUMP 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 V$CONTAINERS
DBA_AUDIT_MGMT_CONFIG_PARAMS DBMS_SCHEDULER V$DATABASE
DBA_FREE_SPACE DBMS_SQL V$INSTANCE
DBA_TABLES DBMS_STATS V$OPTION
DBA_TABLESPACES DBMS_SYS_SQL V$PDBS
DBA_TAB_PARTITIONS DBMS_UTILITY V$VERSION
DBA_USERS    
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 SYS with EXECUTE granted to the AUDIT_ADMIN and EXECUTE_CATALOG_ROLE roles
Source {ORACLE_HOME}/rdbms/admin/dbmsamgt.sql
Subprograms
 
CLEAN_AUDIT_TRAIL (new 12.1 parameters)
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 (new 12.1 parameters)
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 (new 12.1 parameter)
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 (new 12.1 parameter)
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 (new 12.1 procedure)
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 (new 12.1 procedure)
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 (new 12.1 function)
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_LAST_ARCHIVE_TIMESTAMP (new 12.1 parameter)
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('14-OCT-14 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;
/
 
INIT_CLEANUP (new 12.1 parameter)
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 (new 12.1 parameter)
Checks if Audit Cleanup is initialized for the audit trail type 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 (new 12.1 function)
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;
/
 
LOAD_UNIFIED_AUDIT_FILES (new 12.1 procedure)
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 (new 12.1 parameter)
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

Related Topics
Audit Vault
DBMS_FGA
DBMS_SQL
Fine Grained Auditing
Packages

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-2014 Daniel A. Morgan All Rights Reserved