Purging the SOA Infra

This is very useful and straight forward process to clean up SOA database schema. In real world , server are receiving millions of requests in a day and keeping these all data as instances in SOA Suite database schema is very costly. It can affect a performance of the server up to some extent. After few days or month probably you will start receiving table space errors as allotted all the table sapce is already been used by the instances created within SOA database schema. For this reason you need to plan your tablesapce accordingly and generally it should be in between 50 GB - 80 GB in loaded server. And still it requires regular purging for data on the SOA database.

What data does Oracle SOA Suite 11g (PS6 11.1.1.7) store?

Composite instances utilising the SOA Suite Service Engines (BPEL, mediator, human task, rules, BPM, OSB, EDN etc.) will write data to tables residing within the SOAINFRA schema. Each of the engines will either write data to specific engine tables (e.g. the CUBE_INSTANCE table is used solely by the BPEL engine) or common tables that are shared by the SOA Suite engines such as the AUDIT_TRAIL table.

Which data will be purged by the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?

The purge script will delete composite instances that are in the following states:

Completed

Faulted
Terminated by user
Stale
Unknown

The purge script will NOT delete composite instances that are in the following states:

Running (in-flight)
Suspended

Pending Recovery

1. First of all you will required Repository creation utility for 11.1.1.4. This installable contain the all required purging script provided by oracle to purge the database schema  You can find the purge script at location RCU_HOME/rcu/integration/soainfra/sql/soa_purge


2. In SQL*Plus, connect to the database AS SYSDBA:

3. Execute the following SQL commands:
                      GRANT EXECUTE ON DBMS_LOCK to dev_soainfra;
                      GRANT CREATE ANY JOB TO dev_soainfra;

4. RCU_HOME/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql


6.  execute below SQL block and description of each variable is given below

    min_creation_date : minimum date when instance was created
    max_creation_date : Maximum date when instance was created
    batch_size :Batch size used to loop the purge. The default value is 20000.
    max_runtime :Expiration at which the purge script exits the loop. The default value is 60. This value is specified in minutes.
    retention_period :Retention period is only used by the BPEL process service engine only (in addition to using the creation time parameter). The default value is null
    purge_partitioned_component  : Users can invoke the same purge to delete partitioned data. The default value is false


DECLARE

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

BEGIN

   MIN_CREATION_DATE := to_timestamp('2011-06-23','YYYY-MM-DD');
   MAX_CREATION_DATE := to_timestamp('2011-07-03','YYYY-MM-DD');
    max_runtime := 15;
    retention_period := to_timestamp('2011-07-04','YYYY-MM-DD');
   batch_size := 5000;
     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;


 Here is very important to note that this script provided is able to delete instances from database schema however it will not free up the memory of that table / tablespace.

For freeing up the memory you can try this option below on tables.

alter table enable row movement.
alter table shrink space;