Purging on SOA 11g composite specific

If you feel that there are lot of completed instances in database and is becoming huge in size then you can consider to purge it.

Pre-requisites:
1. Purging scripts provided by oracle first needs to be compiled on application server database. You can find those scripts at $ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge location of app server or take it from http://yogesh4984.blogspot.in/2012/12/purging-scripts-for-soa-11g-part-1.html

2. Below grants required on application server database.

GRANT MANAGE SCHEDULER TO username;

GRANT CREATE ANY JOB TO username;

GRANT EXECUTE ON DBMS_LOCK TO username;

Steps to follow for purging based on specific composite of a specific partition:

First check how many instances are there using below script. Always remember to do it in small chunks of approx 30,000 only.

select count(*) from cube_instance where
CREATION_DATE between to_timestamp('2012-07-15','YYYY-MM-DD') and to_timestamp('2012-11-07','YYYY-MM-DD')
AND Composite_name='CompositeName';


Now do purging using below script if above script returns a number less than 30000 else do it for less days. Below script will do purging of process 'CompositeName' under partition 'PartitionName' from 30th Oct 2012 to 7th Nov 2012

DECLARE
l_min_creation_date TIMESTAMP := to_timestamp('2012-10-30','yyyy-mm-dd');
l_max_creation_date TIMESTAMP := to_timestamp('2012-11-07','yyyy-mm-dd');
l_batch_size INTEGER := 50;
l_max_runtime INTEGER := 60;
l_soa_partition_name varchar2(100) := 'PartitionName';
l_composite_name varchar2(100) := 'CompositeName';
BEGIN
-- delete instances for specific composite
soa.delete_instances( min_creation_date => l_min_creation_date
, max_creation_date => l_max_creation_date
, batch_size => l_batch_size
, max_runtime => l_max_runtime
--, retention_period =>
, purge_partitioned_component => FALSE
, composite_name => l_composite_name
--, composite_revision =>
, soa_partition_name => l_soa_partition_name
);
commit;
END;


Sometime you may encounter below error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
In this case try to execute below and then try purging again:
Delete from job_flow_control;

If this does not work then try after 24 hours.


For  more details and information refer
http://docs.oracle.com/cd/E15586_01/integration.1111/e10226/soaadmin_partition.htm