Purge Script For Oracle SOA 11.1.1.6

SOA dehydration store can reach the tablespace limit over the time if not administered properly. Two options are available to deal with such scenarios -


Extend the tablespace which sometimes is not possible due to database/tablespace constraints or some other reasons.
Purge the instances from the dehydration store to free up some space.

Deleting the instances from EM console can be cumbersome if the number of instances to delete is large.
Fortunately Oracle SOA Suite 11.1.1.6 dehydration store purge scripts comes to the rescue. These scripts comes along with the setup and once you install the product it can be located at -

MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge directory

This purge script is only supported for Oracle database. In my case the database is Oracle XE 11g - 64 bit on Windows 7.
Here are the steps to follow before running purge scripts -
Connect as SYS user.
Execute following commands -

GRANT EXECUTE ON DBMS_LOCK to USER;
GRANT CREATE ANY JOB TO USER;
Next you need to create the packages,views and other objects that will delete the instances. For this login as DEV_SOAINFRA user and run the soa_purge_scripts.sql available at location MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge.
Make sure you run the above script in sql*plus (command line) from the above directory else the package will not compile properly. In sqlplus run with this command - SQL> @soa_purge_scripts.sql
Now connect as DEV_SOAINFRA user and run the delete_instances procedure by following block

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

  BEGIN

   MIN_CREATION_DATE := to_timestamp('2013-01-01','YYYY-MM-DD');
   MAX_CREATION_DATE := to_timestamp('2013-09-09','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2013-09-09','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;

You should see the tablespace has now some space available.

More details can be found at this document -
http://docs.oracle.com/cd/E23943_01/admin.1111/e10226/soaadmin_partition.htm