Auditing Oracle SQL*Loader Direct Load Path Events

You can use the CREATE AUDIT POLICY statement to audit Oracle SQL*Loader direct load path events.

About Auditing in Oracle SQL*Loader Direct Path Load Events

You must have the AUDIT_ADMIN role to audit Oracle SQL*Loader direct path events.

To create SQLLoader unified audit policies, you must set the CREATE AUDIT POLICY statement’s COMPONENT clause to DIRECT_LOAD. You can audit direct path load operations only, not other SQLLoader loads, such as conventional path loads.

To access the audit trail, you can query the DIRECT_PATH_NUM_COLUMNS_LOADED column in the UNIFIED_AUDIT_TRAIL data dictionary view.

Oracle SQL*Loader Direct Load Path Unified Audit Trail Events

The unified audit trail can capture SQL*Loader Direct Load Path events.

The unified audit trail captures information about direct path loads that SQLLoader performs (that is, when you set direct=true on the SQLLoader command line or in the SQL*Loader control file).

It also audits Oracle Call Interface (OCI) programs that use the direct path API.

Configuring a Unified Audit Trail Policy for Oracle SQL*Loader Direct Path Events

The CREATE AUDIT POLICY statement ACTIONS COMPONENT clause can create unified audit policies for Oracle SQL*Loader direct path events.

CREATE AUDIT POLICY policy_name
ACTIONS COMPONENT=DIRECT_LOAD { LOAD };

For example:

CREATE AUDIT POLICY audit_sqlldr_pol
 ACTIONS COMPONENT=DIRECT_LOAD LOAD;

You can build more complex policies, such as those that include conditions. Remember that after you create the policy, you must use the AUDIT statement to enable it.

Example: Auditing Oracle SQL*Loader Direct Path Load Operations

The CREATE AUDIT POLICY statement can audit Oracle SQLLoader direct path load operations. Example 27-32 shows how to audit SQLLoader direct path load operations.

Example 27-34 Auditing Oracle SQL*Loader Direct Path Load Operations

CREATE AUDIT POLICY audit_sqlldr_load_pol
 ACTIONS COMPONENT=DIRECT_LOAD LOAD;

AUDIT POLICY audit_sqlldr_load_pol;

How SQL*Loader Direct Path Load Audited Events Appear in the Audit Trail

The UNIFIED_AUDIT_TRAIL data dictionary view lists SQL*Loader direct path load audited events.

The DIRECT_PATH_NUM_COLUMNS_LOADED column of the UNIFIED_AUDIT_TRAIL view shows the number of columns that were loaded using the SQL*Loader direct path load method. For example:

SELECT DBUSERNAME, ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME, DIRECT_PATH_NUM_COLUMNS_LOADED FROM UNIFIED_AUDIT_TRAIL WHERE AUDIT_TYPE = 'DIRECT PATH API';

DBUSERNAME  ACTION_NAME OBJECT_SCHEMA OBJECT_NAME  DIRECT_PATH_NUM_COLUMNS_LOADED
----------- ----------- ------------- ------------ ------------------------------
RLAYTON     INSERT       HR            EMPLOYEES    4