Purging composite instances using "purge_mediator_oracle.sql"

The RCU (Repository Creation Utility) shipped with Oracle SOA Suite 11g provides some PL/SQL procedures to purge composite instances. You probably want to use it to control database growth (for example, purging instances older than 1 month).

1. Install the purge script if not already installed

export MW_HOME=/u01/app/oracle/middleware 

cd $MW_HOME/Oracle_SOA1/rcu/integration/soainfra/sql/purge 

sqlplus dev_soainfra@orcl @purge_mediator_oracle.sql

2. Log in as the SOAINFRA user and run the purge package as follows

oracle@oradev:/home/oracle> sqlplus dev_soainfra@orcl

DECLARE
  filter INSTANCE_FILTER := INSTANCE_FILTER();
  max_instances NUMBER;
  deleted_instances NUMBER;
  purge_partitioned_data BOOLEAN := TRUE; 

BEGIN
  filter.min_created_date := TO_TIMESTAMP('2009-07-01','YYYY-MM-DD');
  filter.max_created_data := TO_TIMESTAMP('2010-12-10','YYYY-MM-DD');
  deleted_instances := FABRIC.DELETE_COMPOSITE_INSTANCES (
    FILTER => filter,
    MAX_INSTANCES => max_instances,
    PURGE_PARTITIONED_DATA => purge_partitioned_data
  );
END;
/

3. Apply filters to your purge script

Be aware that the command above will also purge 'running' instances within that timeframe.

You can add additional filters to the PL/SQL above to limit what you want to delete. For example:

  filter.composite_partition_name :='default';
  filter.composite_name := 'OrderBookingComposite';
  filter.composite_revision := '1.0';
  filter.state := FABRIC.STATE_TERMINATED_BY_USER;
  max_instances := 100;

Some other valid FILTER.STATES also include, in which you can further limit the type of instances you want to purge:

STATE_RECOVERY_REQUIRED
STATE_COMPLETED_SUCCESSFULLY
STATE_FAULTED
STATE_TERMINATED_BY_USER
STATE_SUSPENDED
STATE_STALE

Applicable Versions:

• Oracle SOA Suite 11g (11.1.1.x)

References:

• Chapter 8.11.2 "Procedures For Deleting Instances" of the Oracle® Fusion Middleware Administrator’s Guide for Oracle SOA Suite and Oracle Business Process Management Suite 11g Release 1 (11.1.1) E10226-04