The Summary of DBMS_AUDIT_MGMT Subprograms contains a complete listing of all subprograms in the package.

The Summary of DBMS_AUDIT_MGMT Subprograms contains a complete listing of all subprograms in the package.

Syntax

DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
   interval_number          IN BINARY_INTEGER,
   interval_frequency       IN VARCHAR2);

Parameters

Usage Notes

• The interval frequency that you choose depends on the rate of audit records that are generated in your database. The default setting is for one month. If you have a high audit record rate and are using the default, then too many audit records may be generated in the same partition. In this case, you should change the interval frequency to a more frequent interval, such as one month or one day. If the audit record rate generation is not so high, then you may want to keep it at the default of one month.

Example

The following example sets the partition interval to occur every two months.

BEGIN
 DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
   interval_number       => 2,
   interval_frequency    => 'MONTH');
 END;

The CLEAN_AUDIT_TRAIL procedure is usually called after the SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.

Syntax

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type         IN PLS_INTEGER,
   use_last_arch_timestamp  IN BOOLEAN DEFAULT TRUE,
   container                IN PLS_INTEGER DEFAULT CONTAINER_CURRENT,
   database_id              IN NUMBER DEFAULT NULL,
   container_guid           IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply:

• When cleaning up operating system (OS) or XML audit files, only files in the current audit directory, specified by the AUDIT_FILE_DEST parameter, are cleaned up.

• For Windows platforms, no cleanup is performed when the audit_trail_type parameter is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS. This is because operating system (OS) audit records on Windows are written to the Windows Event Viewer.

• For Unix platforms, no cleanup is performed for cases where the operating system (OS) audit records are written to the syslog. When the audit_trail_type parameter is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, it removes only the *.aud files under the directory specified by the AUDIT_FILE_DEST initialization parameter.

  See Also:

  "AUDIT_SYSLOG_LEVEL" in the Oracle Database Reference

• When the audit_trail_type parameter is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, this procedure only removes XML audit files (*.xml) from the current audit directory.

  Oracle database maintains a book-keeping file (adx_$ORACLE_SID.txt) for the XML audit files. This file is not removed by the cleanup procedure.

• If the cleanup of the unified audit trail is performed when the use_last_arch_timestamp parameter is set to TRUE:

  • If you set the database_id value for the cleanup operation, then this value is used with the last archive timestamp while CLEAN_AUDIT_TRAIL runs. However, for the unified audit records that are present during the cleanup of spillover operating system audit files, the database_id value is ignored. Cleanup for operating system audit files is based on the last archive timestamp only. If you want to have the database_id value used for the cleanup operation of unified audit trail records that are present in the spillover operating system audit files, then load the contents of these files into database tables by using the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure before you run DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.

  • If you do not set the database_id value for the cleanup operation, then CLEAN_AUDIT_TRAIL uses the database ID of the current database container. This ID is used along with the last archive timestamp value while cleaning up the unified audit records that are present in the database tables. However, for unified audit records that are present in the spillover operating system audit records, this database ID is not used and cleanup of these records is based on the last archive timestamp value only. If you want to include the database ID value in the cleanup of unified audit records that are present in the spillover operating system audit files, then load the contents of these files by using the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure before you run DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.

• If the cleanup of the unified audit trail is performed when the use_last_arch_timestamp parameter is set to FALSE:

  • If you set the database_id value for the cleanup operation, then this value is used while CLEAN_AUDIT_TRAIL cleans up the unified audit trail records that are present in database tables. However, the database_id value is not used for the cleanup of unified audit trail records that are present in spillover operating system files. If want the database_id value to be used in the CLEAN_AUDIT_TRAIL operation of unified audit records that are present in spillover operating system audit files, then load the contents of these files to the database tables by using the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure before you run DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.

  • If you do not set the database_id value when you invoke the CLEAN_AUDIT_TRAIL procedure, then Oracle Database purges all unified audit records irrespective of database_id values and irrespective of the location (that is, database tables or spillover operating system audit files) where the unified audit records reside.

Examples

The following example calls the CLEAN_AUDIT_TRAIL procedure to clean up the operating system (OS) audit trail records that were updated before the last archive timestamp.

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp  =>  TRUE);
END;

Audit trail properties are set using the SET_AUDIT_TRAIL_PROPERTY Procedure.

The CLEAR_AUDIT_TRAIL_PROPERTY procedure can optionally reset the property value to it's default value through the use_default_values parameter.

Syntax

DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
   audit_trail_type        IN PLS_INTEGER,
   audit_trail_property    IN PLS_INTEGER,
   use_default_values      IN BOOLEAN DEFAULT FALSE) ;

Parameters

Usage Notes

The following usage notes apply:

• You can use this procedure to clear the value for an audit trail property that you do not wish to use. For example, if you do not want a restriction on the operating system audit file size, then you can use this procedure to reset the OS_FILE_MAX_SIZE property.

  You can also use this procedure to reset an audit trail property to it's default value. You need to set use_default_values to TRUE when invoking the procedure.

• The DB_DELETE_BATCH_SIZE property needs to be individually cleared for the AUDIT_TRAIL_AUD_STD and AUDIT_TRAIL_FGA_STD audit trail types. You cannot clear this property collectively using the AUDIT_TRAIL_DB_STD and AUDIT_TRAIL_ALL audit trail types.

• If you clear the value of the DB_DELETE_BATCH_SIZE property with use_default_value set to FALSE, the default value of DB_DELETE_BATCH_SIZE is still assumed. This is because audit records are always deleted in batches.

• The FILE_DELETE_BATCH_SIZE property needs to be individually cleared for the AUDIT_TRAIL_OS and AUDIT_TRAIL_XML audit trail types. You cannot clear this property collectively using the AUDIT_TRAIL_FILES and AUDIT_TRAIL_ALL audit trail types.

• If you clear the value of the FILE_DELETE_BATCH_SIZE property with use_default_value set to FALSE, the default value of FILE_DELETE_BATCH_SIZE is still assumed. This is because audit files are always deleted in batches.

• You cannot clear the value for the CLEANUP_INTERVAL property.

• You cannot clear the value for the AUDIT_TRAIL_WRITE_MODE property.

Examples

The following example calls the CLEAR_AUDIT_TRAIL_PROPERTY procedure to clear the value for the audit trail property, OS_FILE_MAX_SIZE. The procedure uses a value of FALSE for the USE_DEFAULT_VALUES parameter. This means that there will be no maximum size threshold for operating system (OS) audit files.

BEGIN
DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE        =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   AUDIT_TRAIL_PROPERTY    =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
   USE_DEFAULT_VALUES      =>  FALSE );
END;

Syntax

DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type         IN PLS_INTEGER,
   rac_instance_number      IN PLS_INTEGER DEFAULT NULL,
   container                IN PLS_INTEGER DEFAULT CONTAINER_CURRENT,
   database_id              IN NUMBER DEFAULT NULL,
   container_guid           IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply:

• The timestamp for only one audit_trail_type can be cleared at a time.

• The following are invalid audit_trail_type values for this procedure and cannot be used:

  • AUDIT_TRAIL_ALL

  • AUDIT_TRAIL_DB_STD

  • AUDIT_TRAIL_FILES

Examples

The following example calls the CLEAR_LAST_ARCHIVE_TIMESTAMP procedure to clear the timestamp value for the operating system (OS) audit trail type.

BEGIN
DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   rac_instance_number  =>  1);
END;

This procedure carries out the cleanup operation at intervals specified by the user. It calls the CLEAN_AUDIT_TRAIL Procedure to perform the cleanup operation.

The SET_PURGE_JOB_INTERVAL Procedure is used to modify the frequency of the purge job.

The SET_PURGE_JOB_STATUS Procedure is used to enable or disable the purge job.

The DROP_PURGE_JOB Procedure is used to drop a purge job created with the CREATE_PURGE_JOB procedure.

Syntax

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 DEFAULT TRUE,
   container                   IN PLS_INTEGER DEFAULT CONTAINER_CURRENT) ;

Parameters

Usage Notes

Use this procedure to schedule the CLEAN_AUDIT_TRAIL Procedure for your audit trail records.

Examples

The following example calls the CREATE_PURGE_JOB procedure to create a cleanup job called CLEANUP, for all audit trail types. It sets the audit_trail_purge_interval parameter to 100. This means that the cleanup job is invoked every 100 hours. It also sets the use_last_arch_timestamp parameter value to TRUE. This means that all audit records older than the last archive timestamp are deleted.

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
  audit_trail_type             =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  audit_trail_purge_interval   =>  100 /* hours */,  
  audit_trail_purge_name       =>  'CLEANUP',
  use_last_arch_timestamp      =>  TRUE);
END;

Syntax

DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
   audit_trail_type  IN PLS_INTEGER, 
   container         IN PLS_INTEGER DEFAULT CONTAINER_CURRENT);

Parameters

Usage Notes

You cannot invoke this procedure for AUDIT_TRAIL_UNIFIED. Doing so it will raise ORA-46250 : Invalid value for argument 'AUDIT_TRAIL_TYPE'

Examples

The following example clears the default_cleanup_interval parameter setting for the standard database audit trail:

BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;

Syntax

DBMS_AUDIT_MGMT.DROP_OLD_UNIFIED_AUDIT_TABLES(
   container_guid    IN VARCHAR2) ;

Parameters

Usage Notes

When a pluggable database gets cloned, the unified audit tables get newly created in the new pluggable database. To drop the old unified audit tables, use the DROP_OLD_UNIFIED_AUDIT_TABLES by specifying the old GUID of the PDB from which the clone was created. You can query the historical GUIDs from the DBA_PDB_HISTORY view for the given PDB.

Examples

BEGIN
    DBMS_AUDIT_MGMT.DROP_OLD_UNIFIED_AUDIT_TABLES ( 
      container_guid => 'E4721865A9321CB5E043EFA9E80A2D77'); 
END;

Syntax

DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
   audit_trail_purge_name    IN VARCHAR2) ;

Parameters

Examples

The following example calls the DROP_PURGE_JOB procedure to drop the purge job called CLEANUP.

BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
  AUDIT_TRAIL_PURGE_NAME  => 'CLEANUP');
END;

Syntax

DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL (
   flush_type      IN BINARY_INTEGER   DEFAULT FLUSH_CURRENT_INSTANCE,
   container       IN BINARY_INTEGER   DEFAULT CONTAINER_CURRENT);

Parameters

The audit commit delay time value is useful when determining the last archive timestamp for database audit records.

Syntax

DBMS_AUDIT_MGMT.GET_AUDIT_COMMIT_DELAY
  RETURN NUMBER;

Syntax

DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE(
   audit_trail_type         IN PLS_INTEGER,
   audit_trail_property     IN PLS_INTEGER)
 RETURN NUMBER;

Parameters

Return Values

If the property value is cached in SGA memory, this function will return the value set by the SET_AUDIT_TRAIL_PROPERTY Procedure. Else it will return NULL.

The GET_AUDIT_TRAIL_PROPERTY_VALUE function may return an ORA-46250 error if the audit trail property value has been set to DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL. To find the cleanup interval of the purge job, query SYS.DAM_CLEANUP_JOBS$.

Examples

The following example prints the property value of OS_FILE_MAX_AGE set by the SET_AUDIT_TRAIL_PROPERTY Procedure.

SET_AUDIT_TRAIL_PROPERTY.
SET SERVEROUTPUT ON
DECLARE
 OS_MAX_AGE_VAL NUMBER;
BEGIN
 OS_MAX_AGE_VAL := DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE(
      audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
      audit_trail_property      => DBMS_AUDIT_MGMT. OS_FILE_MAX_AGE);
 IF OS_MAX_AGE_VAL is not NULL THEN
  DBMS_OUTPUT.PUT_LINE('The Maximum Age configured for OS Audit files is: ' ||
                       OS_MAX_AGE_VAL);
 END IF;
END;

Syntax

DBMS_AUDIT_MGMT.GET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type     IN PLS_INTEGER)
 RETURN TIMESTAMP;

Parameters

Return Values

In a database that is opened for READ WRITE, since there will no timestamp stored in SGA memory, this function will return NULL. But in a database that is opened for READ ONLY, if a timestamp is set by the SET_LAST_ARCHIVE_TIMESTAMP Procedure, the timestamp will be returned. Else it will return NULL.

Usage Notes

This function will return NULL on a database that is opened READ WRITE. Use DBA_AUDIT_MGMT_LAST_ARCH_TS view to check the timestamp set in such a case.

Examples

The following example prints the timestamp set by the SET_LAST_ARCHIVE_TIMESTAMP Procedure on a READ ONLY database.

SET SERVEROUTPUT ON
DECLARE
 LAT_TS TIMESTAMP;
BEGIN
 LAT_TS := DBMS_AUDIT_MGMT.GET_LAST_ARCHIVE_TIMESTAMP(
      audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS);
 IF LAT_TS is not NULL THEN
  DBMS_OUTPUT.PUT_LINE('The Last Archive Timestamp is: ' || to_char(LAT_TS));
 END IF;
END;

If the audit trail tables are in the SYSTEM tablespace, then the procedure moves them to the SYSAUX tablespace. If you are using unified auditing, you do not need to run this procedure because the unified audit trail tables are in the SYSAUX tablespace by default. If you are not using unified auditing, refer to Oracle Database Upgrade Guide for documentation which references an environment without unified auditing.

Moving the audit trail tables out of the SYSTEM tablespace enhances overall database performance. The INIT_CLEANUP procedure moves the audit trail tables to the SYSAUX tablespace. If the SET_AUDIT_TRAIL_LOCATION Procedure has already moved the audit tables elsewhere, then no tables are moved.

The SET_AUDIT_TRAIL_LOCATION Procedure enables you to specify an alternate target tablespace for the database audit tables.

The INIT_CLEANUP procedure is currently not relevant for the AUDIT_TRAIL_OS, AUDIT_TRAIL_XML, and AUDIT_TRAIL_FILES audit trail types. No preliminary set up is required for these audit trail types.

This procedure also sets a default cleanup interval for the audit trail records.

Syntax

DBMS_AUDIT_MGMT.INIT_CLEANUP(
   audit_trail_type          IN PLS_INTEGER,
   default_cleanup_interval  IN PLS_INTEGER
   container                 IN PLS_INTEGER DEFAULT CONTAINER_CURRENT);

Parameters

Usage Notes

The following usage notes apply:

• This procedure may involve data movement across tablespaces. This can be a resource intensive operation especially if your database audit trail tables are already populated. Oracle recommends that you invoke the procedure during non-peak hours.

• You should ensure that the SYSAUX tablespace, into which the audit trail tables are being moved, has sufficient space to accommodate the audit trail tables. You should also optimize the SYSAUX tablespace for frequent write operations.

• You can change the default_cleanup_interval later using the SET_AUDIT_TRAIL_PROPERTY Procedure.

• If you do not wish to move the audit trail tables to the SYSAUX tablespace, then you should use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure to move the audit trail tables to another tablespace before calling the INIT_CLEANUP procedure.

• Invoking this procedure with AUDIT_TRAIL_UNIFIED results in ORA-46250. It requires no initializations for cleanup since it is cleanup-ready by default.

Examples

The following example calls the INIT_CLEANUP procedure to set a default_cleanup_interval of 12 hours for all audit trail types:

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      default_cleanup_interval   => 12 /* hours */);
END;

The IS_CLEANUP_INITIALIZED function returns TRUE if the procedure has already been run for the audit trail type. It returns FALSE if the procedure has not been run for the audit trail type.

This function is currently not relevant for the AUDIT_TRAIL_OS, AUDIT_TRAIL_XML, and AUDIT_TRAIL_FILES audit trail types. The function always returns TRUE for these audit trail types. No preliminary set up is required for these audit trail types.

Syntax

DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(
   audit_trail_type  IN PLS_INTEGER
   container         IN PLS_INTEGER DEFAULT CONTAINER_CURRENT)
 RETURN BOOLEAN;

Parameters

Examples

The following example checks to see if the standard database audit trail type has been initialized for cleanup operation. If the audit trail type has not been initialized, then it calls the INIT_CLEANUP Procedure to initialize the audit trail type.

BEGIN
 IF 
   NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
 THEN
   DBMS_AUDIT_MGMT.INIT_CLEANUP(
      audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      default_cleanup_interval  => 12 /* hours */);
 END IF;
END;

Syntax

DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES (
   container       IN BINARY_INTEGER);

Parameters

The SET_AUDIT_TRAIL_LOCATION procedure is not relevant for the AUDIT_TRAIL_OS, AUDIT_TRAIL_XML, and AUDIT_TRAIL_FILES audit trail types. The AUDIT_FILE_DEST initialization parameter is the only way you can specify the destination directory for these audit trail types.

Syntax

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type            IN PLS_INTEGER,
   audit_trail_location_value  IN VARCHAR2) ;

Parameters

Usage Notes

The following usage notes apply:

• By default, audit trail records are written to the SYSAUX tablespace. You can change the tablespace location to a user tablespace using this procedure. However the designated tablespace must be an automatic storage space management (ASSM) tablespace.

• This procedure is valid for the following audit_trail_type values:

  • AUDIT_TRAIL_AUD_STD

  • AUDIT_TRAIL_FGA_STD

  • AUDIT_TRAIL_DB_STD

  • AUDIT_TRAIL_UNIFIED

• For the audit_trail_type values AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_FGA_STD, and AUDIT_TRAIL_DB_STD, you should ensure that the target tablespace, into which the audit trail tables are being moved, has sufficient space to accommodate the audit trail tables. You should also optimize the target tablespace for frequent write operations.

• This procedure involves data movement across tablespaces. For the audit_trail_type values AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_FGA_STD, and AUDIT_TRAIL_DB_STD, this can be a resource intensive operation especially if your database audit trail tables are already populated. Oracle recommends that you invoke the procedure during non-peak hours.

• You optionally can specify an encrypted tablespace for the audit trail location.

• When AUDIT_TRAIL_TYPE is AUDIT_TRAIL_UNIFIED, this procedure sets the tablespace for newer audit records in the unified audit trail but does not move the older audit records. Thus, it is not resource intensive for the unified audit trail.

• The UNIFIED_AUDIT_TRAIL data dictionary view is built on top of an internal relational table. This table is an interval partitioned table (irrespective of database editions) with a default interval of 1 month. This setting means that when you execute the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure, only newly created partitions of the internal table are created in the new tablespace that is set as part of this procedure. Existing partitions of this table remain in the earlier tablespace (SYSAUX is the default tablespace for this internal table). If you want to change this table’s partition interval, then use the DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL procedure.

Examples

The following example moves the database audit trail tables, AUD$ and FGA_LOG$, from the current tablespace to a user-created tablespace called RECORDS:

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
       audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
       audit_trail_location_value  =>  'RECORDS');
END;

The procedure sets the properties OS_FILE_MAX_SIZE, OS_FILE_MAX_AGE, and FILE_DELETE_BATCH_SIZE for operating system (OS) and XML audit trail types. The OS_FILE_MAX_SIZE and OS_FILE_MAX_AGE properties determine the maximum size and age of an audit trail file before a new audit trail file gets created. The FILE_DELETE_BATCH_SIZE property specifies the number of audit trail files that are deleted in one batch.

The procedure sets the properties DB_DELETE_BATCH_SIZE and CLEANUP_INTERVAL for the database audit trail type. DB_DELETE_BATCH_SIZE specifies the batch size in which records get deleted from audit trail tables. This ensures that if a cleanup operation gets interrupted midway, the process does not need to start afresh the next time it is invoked. This is because all batches before the last processed batch are already committed.

The CLEANUP_INTERVAL specifies the frequency, in hours, with which the cleanup procedure is called.

Syntax

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) ;

Parameters

Usage Notes

The following usage notes apply:

• The audit trail properties for which you do not explicitly set values use their default values.

• If you have set both the OS_FILE_MAX_SIZE and OS_FILE_MAX_AGE properties for an operating system (OS) or XML audit trail type, then a new audit trail file gets created depending on which of these two limits is reached first.

  For example, let us take a scenario where OS_FILE_MAX_SIZE is 10000 and OS_FILE_MAX_AGE is 5. If the operating system audit file is already more than 5 days old and has a size of 9000 KB, then a new audit file is opened. This is because one of the limits has been reached.

• The DB_DELETE_BATCH_SIZE property needs to be individually set for the AUDIT_TRAIL_AUD_STD and AUDIT_TRAIL_FGA_STD audit trail types. You cannot set this property collectively using the AUDIT_TRAIL_DB_STD and AUDIT_TRAIL_ALL audit trail types.

• The DB_DELETE_BATCH_SIZE property enables you to control the number of audit records that are deleted in one batch. Setting a large value for this parameter requires increased allocation for the undo log space.

• The FILE_DELETE_BATCH_SIZE property needs to be individually set for the AUDIT_TRAIL_OS and AUDIT_TRAIL_XML audit trail types. You cannot set this property collectively using the AUDIT_TRAIL_FILES and AUDIT_TRAIL_ALL audit trail types.

• The FILE_DELETE_BATCH_SIZE property enables you to control the number of audit files that are deleted in one batch. Setting a very large value may engage the GEN0 background process for a long time.

• In Oracle Database Standard Edition, you can only associate the tablespace for unified auditing once. You should perform this association before you generate any audit records for the unified audit trail. The default tablespace is SYSAUX. After you have associated the tablespace, you cannot modify it on the Standard Edition because the partitioning feature is not supported in the Standard Edition.

Examples

The following example calls the SET_AUDIT_TRAIL_PROPERTY procedure to set the OS_FILE_MAX_SIZE property for the operating system (OS) audit trail. It sets this property value to 102400. This means that a new audit file gets created every time the current audit file size reaches 100 MB.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
       audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
       audit_trail_property        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
       audit_trail_property_value  =>  102400 /* 100MB*/ );
END;

The following example calls the SET_AUDIT_TRAIL_PROPERTY procedure to set the OS_FILE_MAX_AGE property for the operating system (OS) audit trail. It sets this property value to 5. This means that a new audit file gets created every sixth day.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
       audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
       audit_trail_property        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
       audit_trail_property_value  =>  5 /* days */);
END;

The following example calls the SET_AUDIT_TRAIL_PROPERTY procedure to set the DB_DELETE_BATCH_SIZE property for the AUDIT_TRAIL_AUD_STD audit trail. It sets this property value to 100000. This means that during a cleanup operation, audit records are deleted from the SYS.AUD$ table in batches of size 100000.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
       audit_trail_type              => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
       audit_trail_property          => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
       audit_trail_property_value    => 100000 /* delete batch size */);
END;

The CLEAN_AUDIT_TRAIL Procedure uses this timestamp to decide on the audit records to be deleted.

Syntax

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type         IN PLS_INTEGER,
   last_archive_time        IN TIMESTAMP,
   rac_instance_number      IN PLS_INTEGER DEFAULT NULL,
   container                IN PLS_INTEGER DEFAULT CONTAINER_CURRENT,
   database_id              IN NUMBER DEFAULT NULL,
   container_guid           IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply:

• The last_archive_time must be specified in Coordinated Universal Time (UTC) when the audit trail types are AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_FGA_STD, or AUDIT_TRAIL_UNIFIED. This is because the database audit trails store the timestamps in UTC. UTC is also known as Greenwich Mean Time (GMT).

• The last_archive_time must be specified as the local time zone time when the audit trail types are AUDIT_TRAIL_OS or AUDIT_TRAIL_XML. The time zone must be the time zone of the machine where the OS or XML audit files were created. This is because the operating system audit files are cleaned based on the audit file's Last Modification Timestamp property. The Last Modification Timestamp property value is stored in the local time zone of the machine.

• When using an Oracle Real Application Clusters (Oracle RAC) database, Oracle recommends that you use the Network Time Protocol (NTP) to synchronize individual Oracle RAC nodes.

Examples

The following example calls the SET_LAST_ARCHIVE_TIMESTAMP procedure to set the last archive timestamp for the operating system (OS) audit trail type on Oracle RAC instance 1. It uses the TO_TIMESTAMP function to convert a character string into a timestamp value.

A subsequent call to the CLEAN_AUDIT_TRAIL Procedure, with use_last_arch_timestamp set to TRUE, will delete all those OS audit files from the current AUDIT_FILE_DEST directory that were modified before 10-Sep-2012 14:10:10.0.

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   last_archive_time    => TO_TIMESTAMP('12-SEP-0714:10:10.0','DD-MON-RRHH24:MI:SS.FF'),
   rac_instance_number  => 1);
END;

The purge job must have already been created using the CREATE_PURGE_JOB Procedure.

Syntax

DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
   audit_trail_purge_name      IN VARCHAR2,
   audit_trail_interval_value  IN PLS_INTEGER) ;

Parameters

Usage Notes

Use this procedure to modify the audit_trail_purge_interval parameter set using the CREATE_PURGE_JOB Procedure.

Examples

The following example calls the SET_PURGE_JOB_INTERVAL procedure to change the frequency at which the purge job called CLEANUP is invoked. The new interval is set to 24 hours.

BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
  AUDIT_TRAIL_PURGE_NAME       => 'CLEANUP',
  AUDIT_TRAIL_INTERVAL_VALUE   => 24 );
END;

The purge job must have already been created using the CREATE_PURGE_JOB Procedure.

Syntax

DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
   audit_trail_purge_name    IN VARCHAR2,
   audit_trail_status_value  IN PLS_INTEGER) ;

Parameters

Examples

The following example calls the SET_PURGE_JOB_STATUS procedure to enable the CLEANUP purge job.

BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
  audit_trail_purge_name      => 'CLEANUP',
  audit_trail_status_value    => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;

Syntax

In the pre-upgraded Oracle database, these records resided in the common logging infrastructure (CLI) SGA back-end tables.

DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS (
   container_guid       IN VARCHAR2   DEFAULT NULL);

Parameters

Usage Notes

• It is not mandatory to run DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS after an upgrade, but for better read performance of the unified audit trail, Oracle highly recommends that you run this procedure.

• The DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS is designed to be a one-time operation, to be performed after you upgrade from Oracle Database 12c release 12.1.

• There is one CLI back-end table per GUID of the container. You can find the GUIDs for containers by querying the PDB_GUID column of the DBA_PDB_HISTORY data dictionary view. Execute the DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure by passing each of these GUIDs one by one to ensure that you move the unified audit records from these CLI back-end tables to the AUDSYS.AUD$UNIFIED table.

• In a multitenant environment, you must run the DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure only in the container to which the transfer operation applies, whether it is the root or an individual PDB. You cannot run this procedure in the root, for example, to transfer audit records in a PDB.

• If you have a high rate of audit record generation and your database supports partitioning, then you may want to use the DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL procedure to alter the partition interval setting for the internal relational table. See ALTER_PARTITION_INTERVAL Procedure for more information.