ORABPEL dehydration store purge scripts

By default all BPEL instances (messages) in the SOA Suite are persisted in the internal database, the so-called dehydration store. Good practice is to purge older messages, to avoid database sizing problems and to increase performance.

Unfortunately the original Oracle scripts were not sufficient to do the task, so many (consulting) companies created their own purge scripts. The good news is that Oracle realized the need and offers improved purge scripts, which are available to download from Oracle. Please take a look at the Oracle 10G FMW purge strategy whitepaper [ref: note ID 1286265.1], this document also contains the BPEL database schema.

For example, the second option from the whitepaper, multi-threaded purge can be found at Oracle Note: New BPEL 10g Purge Scripts From 10.1.3.5 MLR#2 [ref: note ID 1110833.1]. This script can be scheduled with e.g., crontab, or any other scheduling tool. The start parameters can be configured like

• P_OLDER_THAN := sysdate-21; (purge instances older than 21 days)
• P_ROWNUM := 10000000; (purge up to 10 million instances)
• P_DOP := 3; (use three threads in parallel)
• P_CHUNKSIZE := 1000; (commit per 1000 rows)

This last note contains all three scripts mentioned in the whitepaper:

• SINGLE THREADED LOOPED PURGE PROCEDURE
• MULTI THREADED LOOPED PURGE PROCEDURE
• CTAS (Create Table As Select) PROCEDURE

Afterwards if needed you can fine-tune this script a little.
Tips:

• Create a script for all state instances to purge dev and test environments, modify INSERT INTO temp_cube_instance, change WHERE state >= 5 into WHERE state >= 0
• When purging all instances older than the configured days, it might be handy to keep the process history a little longer, like one year, modify DELETE FROM process_log, change WHERE event_date < p_older_than into WHERE event_date < SYSDATE - 365
• When sensor data is used you might want to include this data in the purge script:
  DELETE FROM activity_sensor_values WHERE creation_date < p_older_than;
DELETE FROM fault_sensor_values WHERE creation_date < p_older_than;
DELETE FROM variable_sensor_values WHERE creation_date < p_older_than;
• When using AIA ErrorHandler you might want to prevent the carthesian product by adding a max to the script. The AIA Error Handler doesn't use unique conversation id's, modify INSERT INTO temp_invoke_message, change FROM temp_cube_instance tci into FROM (SELECT MAX (cikey) cikey, conversation_id FROM temp_cube_instance GROUP BY conversation_id) tci

For the ORAESB schema the scripts can simply be found at $SOA_HOME/integration/esb/sql/other.