Running the Stored Procedure for Oracle

Note: The ec_delete_non_instance_audit stored procedure should only be executed once audit data for process instances and work items has been purged using the ec_delete_audit_stats_data stored procedure. If this is not done it may leave the audit trail for process instances or work items in an inconsistent state

To run the stored procedure, enter the following commands: 

SET SERVEROUTPUT ON
EXEC ec_delete_non_instance_audit(end_time, force_delete, batch_size, dbg)

where:

  • end_time is the date before which the audit data will be purged.

    This is an optional parameter, if not specified all audit data will be deleted.

    The date should be in the format DD-MON-YY HH24:mm:ss

    where:

    • DD is the day of month (for example, 24)
    • MON is the abbreviated month name (for example, JUL)
    • YY is the last 2 digits of the year (for example, 98)
    • HH24 is the hour of day (0-23)
    • mm is the minutes (0-59)
    • ss is the seconds (0-59)

      Following is an example of a valid date: 15-SEP-11 11:00:00

  • force_delete indicates if the audit data should be force deleted.

    In normal operation audit events for process instances should have been purged if the ec_delete_audit_stats_data stored procedure has been executed.

    The ec_delete_non_instance_audit stored procedure will error if it identifies any process instance audit events within the threshold to be purged. If this happens it will not continue with the purge.

    TIBCO recommends you check the audit trail to see if the audit events are required. If you wish to continue with the purge you could pass in 'TRUE' for this option.

    Note: This may leave the audit trail for process instances or work items in an inconsistent state.

    Valid values are:

    • ’TRUE' to switch on force_delete
    • ’FALSE' to switch off force_delete (default value)
  • batch_size is the number of audit events that should be deleted before the transaction is commited

    This is an optional parameter and if not specified will default to 5000.

  • dbg is the debug flag which gives useful debug information. This is switched off by default. Valid values are:
    • ’TRUE' to switch on debug
    • ’'FALSE' to switch off debug (default value)

Examples

The following are examples of usage.

  • To delete audit data before the specified date: 
    exec ec_delete_non_instance_audit('15-SEP-11 11:00:00’);
  • To delete all audit entries: 
    exec ec_delete_non_instance_audit();
  • To delete all audit entries with a different commit batch size (150): 
    exec ec_delete_non_instance_audit(NULL, NULL, 150);
  • To delete all audit entries forcefully: 
    exec ec_delete_non_instance_audit(NULL, 'TRUE');