How to Purge BPEL Instances According to Your BPEL Version (10.1.2, 10.1.3, 11g)

Oracle SOA Platform - Version: 10.1.2.0.0 and later   [Release: AS10gR2 and later ]
Oracle(R) BPEL Process Manager - Version: 10.1.2.0.0 to 11.1.1.3.0   [Release: AS10gR2 to 11gR1]
Information in this document applies to any platform.
***Checked for relevance on 31-Mar-2010***

Goal

The goal of this Note is to explain which script you should run according to the BPEL version that you have installed.

Solution

Oracle BPEL 10.1.2
Use the script provided in the following notes:

• Note 341102.1: How to purge BPEL audit tables to avoid growing tablespace issues ?
• Note 398578.1: How to purge BPEL process instances manually?

Oracle BPEL 10.1.3.3
Use purge_instances_oracle.sql script.
You can edit the script and enter the date time and run the script to purge all instance data from dehydration store which are older than the given date time.

To acquire this script in 10.1.3.3.0,  apply one-off  Patch 6372741 or  install the 10.1.3.3.1 patch set (See Note 553914.1), which automatically include this patch.
After applying the patch purge_instances_oracle.sql script can be found here:

$ORACLE_HOME/bpel/system/database/scripts/purge_instances_oracle.sql

Oracle BPEL 10.1.3.4, 10.1.3.5.0 and 10.1.3.5.1

Use the purge_instances_oracle.sql script which can be found here:

<ORACLE_HOME>/bpel/system/database/scripts/purge_instances_oracle.sql

Here is an example about how to use the purge_instances_oracle.sql script that delete instances older than 2 weeks:

1. Install the purge_instances procedure (you need to run this step only once)
   
   sqlplus orabpel/<password> @purge_instances_oracle.sql
2. Run
   
   SQL> select count(*) from cube_instance
   to know how many instances you have currently (write it down).
3. Call the purge_instances procedure to clear out data older than 2 weeks (14 days)
   
   SQL> call purge_instances(SYSDATE - 14);
4. Run
   
   select count(*) from cube_instance
   again to confirm the number of instances deleted.
5. After the purge_instances process is completed run the online_rebuild_index_oracle.sql to recreate the indexes
   
   SQL> @online_rebuild_index_oracle.sql

More examples about how to use the purge_instances procedure can be found in the script itself.

Note:

• It is recommended to run the script during quiet periods or after business hours.
• To recover the space not used in the tablespaces you can use the regular procedures provided by the database to reorganize a table.

Oracle BPEL 10.1.3.5 MLR#2

See Note 1110833.1: New BPEL 10g Purge Scripts From 10.1.3.5 MLR#2.

Oracle BPEL 11g  (11.1.1.0, 11.1.1.2 , 11.1.1.3, 11.1.1.4, 11.1.1.5 and onwards)

Information is available in the Purging topic of Note 1384379.1 - Oracle Fusion Middleware (FMW) SOA 11g Infrastructure Database: Installation, Maintenance and Administration Gui

de.