Purging Oracle Utilities Business Intelligence Workflow Audit Information (Doc ID 1371336.1)

Last updated on FEBRUARY 05, 2024

Applies to:

Goal

Oracle Utilities Business Intelligence utilizes Oracle Workflow when running Oracle Warehouse Builder Process flows to load extract files into the Data warehouse. Even if extract files are not present, records are created in Audit tables each time a process flow is run.

Depending on the frequency with which process flows are scheduled, these audit tables could grow to become unmanageable, and can cause upgrades or process flow changes to fail when being deployed.

A few of these audit tables include the run-time Oracle workflow audit tables, and can grow very large:

1. WF_ITEM_ATTRIBUTE_VALUES - This table stores the run-time values of the Item Attributes for a particular Process flow.

2. WF_ITEM_ACTIVITY_STATUSES - This table, along with the WF_ITEM_ACTIVITY_STATUSES_H, contain all of the activities executed by a specific occurrence of a Process flow.

3. WF_NOTIFICATION_ATTRIBUTES - This table contains the run-time values of all the Message Attributes for a specific Notification

In addition, Oracle Warehouse Builder also contains audit tables that can also grow very large if not purged periodically.


We recommend that customers immediately start running a set of purge SQL statements at least once a week, that will remove old audit records for completed loads from workflow and warehouse builder audit tables. For customers running Near-Real Time loads from Oracle Utilities Network Management, it will be recommended that this purge process be run once a day (or twice a day if the loads are being run more frequently than every 10 minutes).

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.