Stored Procedure for Oracle

Purge can be run across multiple sessions by creating purge sessions. Once the sessions are created they can be executed in parallel. This allows purge to run on a different set of instances in parallel effectively reducing the time taken to purge.

To run the stored procedure, enter the following commands: 
SET SERVEROUTPUT ON
EXEC create_purge_sessions proc_tpl, end_time, num_sessions, dbg
EXEC start_purge_session session_id, batch_size, cursor_refresh, details, dbg
Note: It is up to you to execute start_purge_session in parallel, for example, multiple sqlplus sessions or similar. The script will not attempt to do this automatically.
where:
  • proc_tpl is a comma-separated list of process template ids. The process template ids can be found in the EC_PROC_TEMPLATE table corresponding to a unique process template name, module name and module version. This is an optional parameter and defaults to all templates if not specified.
  • end_time is the date before which the process instances should have been completed, cancelled or failed. 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 dits 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. This is an optional parameter, if not specified all completed, cancelled and failed instances will be deleted.

  • num_sessions is the number of purge sessions to be created. This is an optional parameter and if not specified will default to 1.
  • session_id is the id of the session to be started.
    Note: This is zero (0) based, so if 3 sessions were created using create_purge_sessions, the session ids would be 0, 1 and 2.
    This is an optional parameter and if not specified will default to 0.
  • batch_size is the number of process instances that should be deleted before the transaction is committed. This is an optional parameter which defaults to 100 if not specified.
  • cursor_refresh is the time in seconds for the cursor to be refreshed. This is an optional parameter that defaults to 7200 (2 hours) if not specified.
  • details is user information to be identified with the purge session. This can be any user information desired; it is recorded in the audit trail.
  • dbg is the debug flag which gives useful debug information. This flag is switched off by default. Set the value to:
    • TRUE to switch on debug.
    • FALSE to switch off debug (default).