Purge BPEL Process Manager Instances from SOA

The objective of this BLOG thread is to provide step by step instructions as well as examples showing how to purge Oracle BPEL Process Manager 11g instances using purge scripts.

  1.  Log in to the schema.

If you're using 11g Oracle BPEL Process Manager installed as part of an 11g Oracle SOA Suite or 11g Oracle BPM Suite, you'll start by granting the privileges to the <DEV>_SOAINFRA user, then logging in to the <DEV>_SOAINFRA schema.

sqlplus /nolog
>sqlplus / as sysdba

SQL> GRANT EXECUTE ON DBMS_LOCK TO <DEV>_SOAINFRA;

Grant succeeded.

SQL> GRANT CREATE ANY JOB TO <DEV>_SOAINFRA;

Grant succeeded.

SQL>exit

>sqlplus <DEV>_SOAINFRA/<password>

  2.  Install the Procedures (First time Users Only )

Run the script to install the procedures.  This is a one time installation step.  If you're not sure where the scripts to install the procedures are located in your environment

SQL> @<Oracle SOA Home>/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql
Procedure created.
Function created.
Type created.
Type body created.
PL/SQL procedure successfully completed.
Package created.
Package body created.
SQL>

  3. Identify and/or Customize the Purge Procedure

You will need to identify the syntax and possibly customize the procedure according to your purging goal.  Depending upon the version, there may be several possible procedures available for use.  Review the text of the installation script or the product documentation for more details on the procedures.

   4.  Call the Procedure

Once the procedure is loaded and you've identified the method you'll use to purge instances, you'll run the procedure.  You may want to perform a simple select statement to count the existing instances prior to purging.

To clear out instances from a specific date/time (ie...January 1, 2011 to January 31, 2011):

SQL> call soa.delete_instances(to_timestamp('2010-01-01','YYYY-MM-DD'), to_timestamp('2010-01-31','YYYY-MM-DD'));

To call the purge_instances procedure to clear out data older than 2 weeks (14 days)

SQL> call soa.delete_instances(SYSDATE -14, SYSDATE);

• To clear out instances using the explicit single threaded script as shown in the readme (ie.. January 1, 2011 to January 31, 2011):

DECLARE

MAX_CREATION_DATE timestamp;
MIN_CREATION_DATE timestamp;
batch_size integer;
max_runtime integer;
retention_period timestamp;

BEGIN

MIN_CREATION_DATE := to_timestamp('2010-01-01','YYYY-MM-DD');
MAX_CREATION_DATE := to_timestamp('2010-01-31','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
batch_size := 10000;
soa.delete_instances(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
purge_partitioned_component => false);
END;
/

Example
DECLARE

MAX_CREATION_DATE timestamp;
MIN_CREATION_DATE timestamp;
batch_size integer;
max_runtime integer;
retention_period timestamp;

BEGIN

MIN_CREATION_DATE := to_timestamp('2010-01-01','YYYY-MM-DD');
MAX_CREATION_DATE := to_timestamp('2011-09-23','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-09-23','YYYY-MM-DD'); //Retain everything before 2010-09-23
batch_size := 10000;
soa.delete_instances(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
purge_partitioned_component => false);
END;
/

• To clear out instances using the explicit parallel script as shown in the readme (ie.. January 1, 2011 to January 31, 2011):

DECLARE

max_creation_date timestamp;
min_creation_date timestamp;
retention_period timestamp;
BEGIN

min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');

soa.delete_instances_in_parallel(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => 10000,
max_runtime => 60,
retention_period => retention_period,
DOP => 3,
max_count => 1000000,
purge_partitioned_component => false);

END;
/

5. Recover Space as needed - optional

Space recovery is not included in the Purge Script as this is a specific Database Maintenance task that needs to be performed by a DBA. The following sample instructions can help in space recovery:

1. Run an index rebuild, see:
Oracle Database Administrator's Guide
11g Release 2 (11.2)


2. Running PL/SQL for space release:
alter table <TABLE_NAME> deallocate unused;
alter table <TABLE_NAME> enable row movement;
alter table <TABLE_NAME> shrink space compact;
alter table <TABLE_NAME> shrink space;
alter table <TABLE_NAME> disable row movement;

For questions, comments and feedback  please contact:
 Harvinder Singh Saluja