SOA 11g - SOA Data Base Schema Purging

This post is about how to control the SOA database schema and its growth while running SOA Suite 11g. All the instructions given here are specifically for SOA Suite 11g version 11.1.1.4. From this version onward there are new scripts provided by Oracle and only updated scripts should be used as this version has schema and table structure changes.

Purging composites instances in Oracle SOA SUITE 11g

There are several ways to purge large amounts of instances in SOA Suite 11g database - dehydration storage;

1. By dropping the SOA Suite repository and next, to recreate it using RCU creation utility providing exactly the same credentials.
2. Using Enterprise Manager console; Delete with options 
3. Deleting large number of instances using purge script

Dropping the SOA Suite repository Using RCU: Dropping the SOA Suite repository using RCU is straight forward. Just run the RCU utility select "Drop" instead of "Create" and follow the installer.

Using Enterprise Manager Console: Here, first option, using Enterprise Manager console is ok for small amount of instances where you want to remove one or two thousand instances. For large number of instances it will throw Time out error. There is a option provided in EM console for increasing the timeout period but still its not enough for large amount of data.

Deleting Large Number Of Instances Using Purge Script: 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 space 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.

Below is the process for purging / deleting the composite instances from SOA Database schema.

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.

Note : this script cannot run with Microsoft SQL Server and IBM DB2 Database.

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

5. execute SET SERVEROUTPUT ON on SQL prompt

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;