Skip Headers

Oracle® Audit Vault Administrator's Guide Release 10.2.3.1 Part Number E13841-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 14 DBMS_AUDIT_MGMT PL/SQL Package

This chapter contains:

- About Using the DBMS_AUDIT_MGMT PL/SQL Package

- DBMS_AUDIT_MGMT PL/SQL Package Security Model

- DBMS_AUDIT_MGMT PL/SQL Package Constants

- Summary of DBMS_AUDIT_MGMT PL/SQL Package Subprograms

See Also:

- Section 4.8 for the general steps you must take to purge audit trail data

- Chapter 13, "DBMS_AUDIT_MGMT Data Dictionary Views," for `DBMS_AUDIT_MGMT`-specific data dictionary views

## 14.1 About Using the DBMS_AUDIT_MGMT PL/SQL Package

The `DBMS_AUDIT_MGMT` PL/SQL package provides a set of subprograms that you can use to manage the Oracle Database audit trail data. It enables you to:

- Archive and purge (clean) the audit trail data for all of the supported audit trail formats.

- Move the database audit trail tables out of the `SYSTEM` tablespace to a different tablespace. This improves performance and enables you to dedicate an optimized tablespace for audit records.

- For the operating system audit trail, set a maximum size and age of the file before a new operating system audit trial file is created.

- For the database audit trail, set a record batch size in which records are deleted from audit trail tables.

- Set an archive timestamp for archived audit records, and then delete audit trail records based on the last archive timestamp. The last archive timestamp indicates when the audit records were last archived.

- Configure and schedule periodic purge jobs to delete audit records.

- Diagnose errors by using trace files.

## 14.2 DBMS_AUDIT_MGMT PL/SQL Package Security Model

All `DBMS_AUDIT_MGMT` subprograms require the user to have `EXECUTE` privilege on the `DBMS_AUDIT_MGMT` package. The `SYSDBA` privilege has `EXECUTE` privileges on the package by default.

Only audit administrators should have `EXECUTE` privileges over the `DBMS_AUDIT_MGMT` package.

## 14.3 DBMS_AUDIT_MGMT PL/SQL Package Constants

The `DBMS_AUDIT_MGMT` package defines several enumerated constants that should be used for specifying parameter values. Enumerated constants must be prefixed with the package name, for example, `DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD`.

The `DBMS_AUDIT_MGMT` package uses the constants shown in the following tables:

- Table 14-1, "DBMS_AUDIT_MGMT Constants - Types of Audit Trails"

- Table 14-2, "DBMS_AUDIT_MGMT Constants - Audit Trail Properties"

- Table 14-3, "DBMS_AUDIT_MGMT Constants - Purge Job Status"

- Table 14-4, "DBMS_AUDIT_MGMT Constants - Trace Level Values"

Table 14-1 lists the audit trail type constants. Audit trails are classified by where Oracle Database writes the audit records: to database tables, operating system files, or XML files.

Table 14-1 DBMS_AUDIT_MGMT Constants - Types of Audit Trails

Constant | Data Type | Value | Description
--- | --- | --- | ---
`AUDIT_TRAIL_ALL` | `PLS_INTEGER` | `15` | All audit trail types. This includes the standard database audit trail (`SYS.AUD$` and `SYS.FGA_LOG$` tables), operating system (OS) audit trail, and XML audit trail.
`AUDIT_TRAIL_AUD_STD` | `PLS_INTEGER` | `1` | Standard database audit records in the `SYS.AUD$` table
`AUDIT_TRAIL_DB_STD` | `PLS_INTEGER` | `3` | Both standard audit (`SYS.AUD$`) and FGA audit (`SYS.FGA_LOG$`) records
`AUDIT_TRAIL_FGA_STD` | `PLS_INTEGER` | `2` | Standard database fine-grained auditing (FGA) records in the `SYS.FGA_LOG$` table
`AUDIT_TRAIL_FILES` | `PLS_INTEGER` | `12` | Both operating system (OS) and XML audit trails
`AUDIT_TRAIL_OS` | `PLS_INTEGER` | 4 | Operating system audit trail. This refers to the audit records stored in operating system files.
`AUDIT_TRAIL_XML` | `PLS_INTEGER` | 8 | XML audit trail. This refers to the audit records stored in XML files.

Table 14-2 lists the constants related to audit trail properties. Audit trail properties determine the audit configuration settings.

Table 14-2 DBMS_AUDIT_MGMT Constants - Audit Trail Properties

Constant | Type | Value | Description
--- | --- | --- | ---
`CLEANUP_INTERVAL` | `PLS_INTEGER` | `21` | Interval, in hours, after which the cleanup job is called to clear audit records in the specified audit trail
`DB_DELETE_BATCH_SIZE` | `PLS_INTEGER` | `23` | Specifies the batch size to be used for deleting audit records in database audit tables. The audit records are deleted in batches of size equal to `DB_DELETE_BATCH_SIZE`.
`OS_FILE_MAX_AGE` | `PLS_INTEGER` | `17` | Specifies the maximum number of days for which an operating system (OS) or XML audit file can be kept open before a new audit file gets created
`OS_FILE_MAX_SIZE` | `PLS_INTEGER` | `16` | Specifies the maximum size to which an operating system (OS) or XML audit file can grow before a new file is opened

Table 14-3 lists the constants related to purge job status values. The audit trail purge job cleans up the audit trail.

Table 14-3 DBMS_AUDIT_MGMT Constants - Purge Job Status

Constant | Type | Value | Description
--- | --- | --- | ---
`PURGE_JOB_DISABLE` | `PLS_INTEGER` | `32` | Disables a purge job
`PURGE_JOB_ENABLE` | `PLS_INTEGER` | `31` | Enables a purge job

Table 14-4 lists the constants related to trace level values. The `DBMS_AUDIT_MGMT` package enables you to trace operations for diagnostic purposes.

Table 14-4 DBMS_AUDIT_MGMT Constants - Trace Level Values

Constant | Type | Value | Description
--- | --- | --- | ---
`TRACE_LEVEL_DEBUG` | `PLS_INTEGER` | `1` | Logs detailed debug messages
`TRACE_LEVEL_ERROR` | `PLS_INTEGER` | `2` | Logs only error messages

## 14.4 Summary of DBMS_AUDIT_MGMT PL/SQL Package Subprograms

Table 14-5 lists the `DBMS_AUDIT_MGMT` package subprograms.

Table 14-5 DBMS_AUDIT_MGMT Package Subprograms

Subprogram | Description
--- | ---
CLEAN_AUDIT_TRAIL Procedure | Deletes audit trail records that have been archived
CLEAR_AUDIT_TRAIL_PROPERTY Procedure | Clears the value for the audit trail property that you specify
CLEAR_LAST_ARCHIVE_TIMESTAMP Procedure | Clears the timestamp set by the `SET_LAST_ARCHIVE_TIMESTAMP` procedure
CREATE_PURGE_JOB Procedure | Creates a purge job for periodically deleting the audit trail records
DEINIT_CLEANUP Procedure | Undoes the setup and initialization performed by the `INIT_CLEANUP` procedure
DROP_PURGE_JOB Procedure | Drops the purge job that was created using the `CREATE_PURGE_JOB` procedure
GET_AUDIT_COMMIT_DELAY Function | Returns the number of seconds allowed for a `COMMIT` operation to take place when an audit record is written to the database
INIT_CLEANUP Procedure | Sets up the audit management infrastructure and sets a default cleanup interval for the audit trail records
IS_CLEANUP_INITIALIZED Function | Checks to see if the `INIT_CLEANUP` procedure has been run for an audit trail type
SET_AUDIT_TRAIL_LOCATION Procedure | Moves the audit trail tables from their current tablespace to a user-specified tablespace
SET_AUDIT_TRAIL_PROPERTY Procedure | Sets the audit trail properties for the audit trail type that you specify
SET_DEBUG_LEVEL Procedure | Sets the trace level for the `DBMS_AUDIT_MGMT` package
SET_LAST_ARCHIVE_TIMESTAMP Procedure | Sets a timestamp indicating when the audit records were last archived
SET_PURGE_JOB_INTERVAL Procedure | Sets the interval at which the `CLEAN_AUDIT_TRAIL` is called for the purge job that you specify
SET_PURGE_JOB_STATUS Procedure | Enables or disables the purge job that you specify

### 14.4.1 CLEAN_AUDIT_TRAIL Procedure

The `CLEAN_AUDIT_TRAIL` procedure deletes audit trail records that have been archived.

Typically, you run the `CLEAN_AUDIT_TRAIL` procedure is after you run the `SET_LAST_ARCHIVE_TIMESTAMP` procedure, which sets 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) ;

Parameters

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the cleanup operation needs to be performed. Table 14-1 lists audit trail types.
`use_last_arch_timestamp` | Specify whether the last archived timestamp should be used to determine the records that should be deleted. A value of `TRUE` indicates that only audit records created before the last archive timestamp should be deleted. A value of `FALSE` indicates that all audit records should be deleted. The default value is `TRUE`.

Usage Notes

None

Examples

The following example calls the `CLEAN_AUDIT_TRAIL` procedure to clean up the operating system (OS) audit trail records that were created 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;
    /

### 14.4.2 CLEAR_AUDIT_TRAIL_PROPERTY Procedure

The `CLEAR_AUDIT_TRAIL_PROPERTY` procedure clears the value for the audit trail property that is specified. 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 its 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

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the property needs to be cleared. Table 14-1 lists the audit trail types.
`audit_trail_property` | Enter the audit trail property whose value needs to be cleared. You cannot clear the value for the `CLEANUP_INTERVAL` property. Table 14-2 lists the audit trail properties.
`use_default_values` | Specify whether the default value of the `audit_trail_property` should be used in place of the cleared value. A value of `TRUE` causes the default value of the parameter to be used. A value of `FALSE` causes the `audit_trail_property` to have no value. The default value for this parameter is `FALSE`.

Usage Notes

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

- You cannot clear the value for the `CLEANUP_INTERVAL` 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` because the procedure uses a value of `FALSE` for the `USE_DEFAULT_VALUES` parameter. This means that the `OS_FILE_MAX_SIZE` property will no longer determine the size of the 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;
    /

### 14.4.3 CLEAR_LAST_ARCHIVE_TIMESTAMP Procedure

The `CLEAR_LAST_ARCHIVE_TIMESTAMP` procedure clears the timestamp set by the `SET_LAST_ARCHIVE_TIMESTAMP` procedure.

Syntax

    DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
       audit_trail_type     IN PLS_INTEGER,
       rac_instance_number  IN PLS_INTEGER DEFAULT 0) ;

Parameters

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the timestamp needs to be cleared. Table 14-1 lists the audit trail types.
`rac_instance_number` | Enter the instance number for the Oracle Real Application Clusters (Oracle RAC) instance. The default value is 0, which is used for the database audit trail type. The `rac_instance_number` is not relevant for the database audit trail type, as the database audit trail tables are shared by all Oracle RAC instances.

Usage Notes

None

Example

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 /* single instance database */);
    END;
    /

### 14.4.4 CREATE_PURGE_JOB Procedure

The `CREATE_PURGE_JOB` procedure creates a purge job for periodically deleting the audit trail records. The procedure can use the timestamp value set by the `SET_LAST_ARCHIVE_TIMESTAMP` procedure to determine the records to be deleted.

This procedure performs the cleanup operation on an intervals that you specify. 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) ;

Parameters

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the purge job needs to be created. Table 14-1 lists the audit trail types.
`audit_trail_purge_interval` | Enter the interval, in hours, at which the clean up procedure is called. A lower value means that the cleanup is performed more often.
`audit_trail_purge_name` | A name to identify the purge job
`use_last_arch_timestamp` | Specify whether the last archived timestamp should be used to determine the records that should be deleted. A value of `TRUE` indicates that only audit records created before the last archive timestamp should be deleted. A value of `FALSE` indicates that all audit records should be deleted. The default value is `TRUE`.

Usage Notes

Use this procedure to schedule the `CLEAN_AUDIT_TRAIL` procedure for your audit 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 to invoke that the cleanup job every 100 hours. It also sets the `use_last_arch_timestamp` parameter value to `TRUE`, so 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;
    /

### 14.4.5 DEINIT_CLEANUP Procedure

The `DEINIT_CLEANUP` procedure undoes the setup and initialization performed by the `INIT_CLEANUP` procedure. The `DEINIT_CLEANUP` procedure clears the value of the `default_cleanup_interval` parameter. However, it does not move the audit trail tables back to their original location.

Syntax

    DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
       audit_trail_type  IN PLS_INTEGER) ;

Parameters

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the procedure needs to be called. Table 14-1 lists the audit trail types.

Usage Notes

You can change the `default_cleanup_interval` later using the `SET_AUDIT_TRAIL_PROPERTY` procedure.

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

### 14.4.6 DROP_PURGE_JOB Procedure

The `DROP_PURGE_JOB` procedure drops the purge job that was created using the `CREATE_PURGE_JOB` procedure. The name of the purge job is passed as an argument.

Syntax

    DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
       audit_trail_purge_name    IN VARCHAR2) ;

Parameter

Parameter | Description
--- | ---
`audit_trail_purge_name` | Enter the name of the purge job to be deleted. This is the purge job name that you specified with the `CREATE_PURGE_JOB` procedure.

Usage Notes

None

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

### 14.4.7 GET_AUDIT_COMMIT_DELAY Function

The `GET_AUDIT_COMMIT_DELAY` function returns the number of seconds allowed for a `COMMIT` operation to take place when an audit record is written to the database. The default time is 5 seconds. If the `COMMIT` operation exceeds this time, then Oracle Database writes each audit record to an operating system file, even if the `AUDIT_TRAIL` initialization parameter is set to `DB` or `DB, EXTENDED`.

Syntax

    DBMS_AUDIT_MGMT.GET_AUDIT_COMMIT_DELAY
      RETURN NUMBER;

Parameters

None

Usage Notes

None

Examples

None

### 14.4.8 INIT_CLEANUP Procedure

The `INIT_CLEANUP` procedure sets up the audit management infrastructure and sets a default cleanup interval for the audit trail records. The procedure also moves the audit trail tables out of the `SYSTEM` tablespace.

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 they are not moved back to the `SYSAUX` tablespace.

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

Parameters

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the clean up operation needs to be initialized. Table 14-1 lists audit trail types.
`default_cleanup_interval` | Enter the default time interval, in hours, after which the cleanup procedure should be called. The minimum value is 1 and the maximum is 999.

Usage Notes

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

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

### 14.4.9 IS_CLEANUP_INITIALIZED Function

The `IS_CLEANUP_INITIALIZED` function checks if the `INIT_CLEANUP` procedure has been run for an audit trail type. 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 does not apply to 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)
     RETURN BOOLEAN;

Parameters

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the function needs to be called. Table 14-1 lists the audit trail types.

Usage Notes

None

Examples

The following example checks if the standard database audit trail type has been initialized for a cleanup operation. If the audit trail type has not been initialized, then the example 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;
    /

### 14.4.10 SET_AUDIT_TRAIL_LOCATION Procedure

The `SET_AUDIT_TRAIL_LOCATION` procedure moves the audit trail tables from their current tablespace to a user-specified tablespace.

The `SET_AUDIT_TRAIL_LOCATION` procedure does not apply to the `AUDIT_TRAIL_OS`, `AUDIT_TRAIL_XML`, and `AUDIT_TRAIL_FILES` audit trail types. The `AUDIT_FILE_DEST` initialization parameter can be used to 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

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the audit trail location needs to be set. Table 14-1 lists audit trail types.
`audit_trail_location_value` | Enter the target location or tablespace for the audit trail records

Usage Notes

- This procedure involves 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 run the procedure during nonpeak hours.

- 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. For more information, see Oracle Database Performance Tuning Guide and Oracle Database Administrator's Guide.

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

### 14.4.11 SET_AUDIT_TRAIL_PROPERTY Procedure

The `SET_AUDIT_TRAIL_PROPERTY` procedure sets the audit trail properties for the audit trail type that you specify.

The procedure sets properties such as `OS_FILE_MAX_SIZE` and `OS_FILE_MAX_AGE` for operating system (OS) and XML audit trail types. These properties determine the maximum size and age of an audit trail file before a new audit trail file is created.

The procedure sets properties such as `DB_DELETE_BATCH_SIZE` and `CLEANUP_INTERVAL` for the database audit trail type. `DB_DELETE_BATCH_SIZE` specifies the batch size in which records are deleted from audit trail tables. This ensures that if a cleanup operation is 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 deleted.

The `CLEANUP_INTERVAL` property value 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

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the property needs to be set. Table 14-1 lists audit trail types.
`audit_trail_property` | Enter the audit trail property that is being set. Table 14-2 lists audit trail properties.
`audit_trail_property_value` | Enter the value of the property specified using `audit_trail_property`. The following are valid values for audit trail properties: `OS_FILE_MAX_SIZE` can have a minimum value of 1 and maximum value of 2000000. The default value is 10000. `OS_FILE_MAX_SIZE` is measured in kilobytes (KB). `OS_FILE_MAX_AGE` can have a minimum value of 1 and a maximum value of 497. The default value is 5. `OS_FILE_MAX_AGE` is measured in days. `DB_DELETE_BATCH_SIZE` can have a minimum value of 100 and a maximum value of 1000000. The default value is 10000. `DB_DELETE_BATCH_SIZE` is measured as the number of audit records that are deleted in one batch. `CLEANUP_INTERVAL` can have a minimum value of 1 and a maximum value of 999. The default value is set using the `INIT_CLEANUP` procedure. `CLEANUP_INTERVAL` is measured in hours.

Usage Notes

- 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 is created, depending on which of these two limits is reached first.

  For example, suppose `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, because one of the limits has been reached.

- You must individually set the `DB_DELETE_BATCH_SIZE` property 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.

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, so that a new audit file is 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, so that a new audit file is 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. Thus, during a cleanup operation, audit records are deleted from the `SYS.AUD$` table in batches of 100000 records.

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

### 14.4.12 SET_DEBUG_LEVEL Procedure

The `SET_DEBUG_LEVEL` procedure sets the trace level for the `DBMS_AUDIT_MGMT` package. The default trace level, `TRACE_LEVEL_ERROR`, logs only the error messages as trace messages. The debug trace level, `TRACE_LEVEL_DEBUG`, logs detailed debug messages.

Syntax

    DBMS_AUDIT_MGMT.SET_DEBUG_LEVEL(
       debug_level IN PLS_INTEGER DEFAULT TRACE_LEVEL_ERROR);

Parameters

Parameter | Description
--- | ---
`debug_level` | Enter the trace level. `TRACE_LEVEL_ERROR` logs only the error messages as trace messages. `TRACE_LEVEL_DEBUG` logs detailed debug messages.

Usage Notes

None

Examples

The following example calls the `SET_DEBUG_LEVEL` procedure to enable enhanced debugging.

    BEGIN
    DBMS_AUDIT_MGMT.SET_DEBUG_LEVEL(
      debug_level   => DBMS_AUDIT_MGMT.TRACE_LEVEL_DEBUG);
    END;
    /

### 14.4.13 SET_LAST_ARCHIVE_TIMESTAMP Procedure

The `SET_LAST_ARCHIVE_TIMESTAMP` procedure sets a timestamp indicating when the audit records were last archived. The audit administrator provides the timestamp to be attached to the audit records. The `CLEAN_AUDIT_TRAIL` procedure uses this timestamp to determine 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 0) ;

Parameters

Parameter | Description
--- | ---
`audit_trail_type` | Enter the audit trail type for which the timestamp needs to be set. Table 14-1 lists audit trail types.
`last_archive_time` | Enter the `TIMESTAMP` value to be attached to the audit records. This indicates the last time when the audit records were archived.
`rac_instance_number` | Enter the instance number for the Oracle Real Application Clusters (Oracle RAC) instance. The default value is 0, which is used for the database audit trail type. The `rac_instance_number` parameter is not relevant for the database audit trail type, as the database audit trail tables are shared by all Oracle RAC instances.

Usage Notes

- You must set the `last_archive_time` parameter using Coordinated Universal Time (UTC) for the `AUDIT_TRAIL_AUD_STD` and `AUDIT_TRAIL_FGA_STD` audit trail types. This is because the database audit trails store the timestamps in UTC. UTC is also known as Greenwich Mean Time (GMT).

- You must set the `last_archive_time` parameter using the local time zone time for the `AUDIT_TRAIL_OS` and `AUDIT_TRAIL_XML` audit trail types. This is because the operating system audit records are stored as files that use the local time zone for their last modification timestamps.

- If you are using and Oracle RAC database, Oracle recommends that you use the Network Time Protocol (NTP) to synchronize individual Oracle RAC nodes.

Examples

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

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

### 14.4.14 SET_PURGE_JOB_INTERVAL Procedure

The `SET_PURGE_JOB_INTERVAL` procedure sets the interval at which the `CLEAN_AUDIT_TRAIL` procedure is called for the purge job specified. 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

Parameter | Description
--- | ---
`audit_trail_purge_name` | Enter the name of the purge job for which the interval is being set. This is the purge job name that you specified with the `CREATE_PURGE_JOB` procedure.
`audit_trail_interval_value` | Enter the interval, in hours, at which the cleanup procedure should be called. This value modifies the `audit_trail_purge_interval` parameter set using the `CREATE_PURGE_JOB` procedure.

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

### 14.4.15 SET_PURGE_JOB_STATUS Procedure

The `SET_PURGE_JOB_STATUS` procedure enables or disables the specified purge job. 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

Parameter | Description
--- | ---
`audit_trail_purge_name` | Enter the name of the purge job for which the status is being set. This is the purge job name that you specified with the `CREATE_PURGE_JOB` procedure.
`audit_trail_status_value` | Enter one of the values specified in Table 14-3. The value `PURGE_JOB_ENABLE` enables the specified purge job. The value `PURGE_JOB_DISABLE` disables the specified purge job.

Usage Notes

None

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