How to find purgeable instances in SOA/BPM 12c

If you are familiar with SOA/BPM 11g purging, after you have upgraded/implemented SOA/BPM 12c, you will not be able to use most of the SQL for 11g to determine the purgeable instances.  This is because SOA/BPM 12c is no longer using composite_instance table for composite instance tracking.

In SOA/BPM 12c, a common component is used to track the state associated with a business flow and report audit information.  This design will reduce the instance tracking data generated and stored in the database, and improve purge performance by minimizing the number of tables that need to be accessed.  Component instance state will no longer be stored in individual table for instance tracking purpose, the overall flow state will be stored in SCA_FLOW_INSTANCE table.

In SCA_FLOW_INSTANCE table, the “active_component_instances” column keeps track of how many component instances are still in a running/active state. These are the instances in one of the following states:


When the “active_component_instances” value reaches 0, this indicates that the Flow is no longer executing. There is another column called “recoverable_faults”, this column keeps track of how many faults can be recovered. This information together with the “active_component_instances” is used to determine whether the Flow can be purged or not.

The SCA_FLOW_ASSOC table is used to record the association between the original Flow that creates the BPEL component instance and the correlated Flow. The SCA_FLOW_ASSOC table is used by the purge logic to ensure that all correlated Flows are purged together when none of the flow is in an active state.

Another important thing to take note: if you create a SOAINFRA schema with LARGE database profile, all transactional tables will be created with range-partition. If you decide to run the SOA purging with the purge script either manually by running the stored procedure or by using auto purge function which can be configured in Oracle Enterprise Manager Fusion Middleware Control, you will need to set the purge_partitioned_component => true (default is false), otherwise the purge logic will skip all partitioned tables when the purge script run and no flow instance will be purged.  You will be able to find all the partition tables in your SOAINFRA schema by using the following SQL:

select table_name from user_tables where partitioned = 'YES';

You can use the following sample PL/SQL to determine whether the SCA_FLOW_INSTANCE has been partitioned and the number of purgeable flow instances in your SOAINFRA schema.

set serveroutput on;
  batch_size        INTEGER;
  retention_period  TIMESTAMP;
  purgeable_instance INTEGER;
  table_partitioned INTEGER;
  MAX_CREATION_DATE := to_timestamp('2015-12-27','YYYY-MM-DD');
  MIN_CREATION_DATE := to_timestamp('2015-12-01','YYYY-MM-DD');
  retention_period  := to_timestamp('2015-12-27','YYYY-MM-DD');
  batch_size        := 100000;
  if retention_period < max_creation_date then
    retention_period := max_creation_date;  
  end if;
  select count(table_name) into table_partitioned from user_tables where partitioned = 'YES' and table_name='SCA_FLOW_INSTANCE';
  if table_partitioned > 0 then
   DBMS_OUTPUT.PUT_LINE ('SCA_FLOW_INSTANCE is not partitioned ');
  end if;
  SELECT Count(s.flow_id) into purgeable_instance
  FROM sca_flow_instance s
  WHERE s.created_time            >= MIN_CREATION_DATE
  AND s.created_time              <= MAX_CREATION_DATE
  AND s.updated_time              <= retention_period
  AND s.active_component_instances = 0
  AND s.flow_id NOT IN  (SELECT r.flow_id FROM temp_prune_running_insts r)
  AND s.flow_id IN
    (SELECT c.flow_id FROM sca_flow_to_cpst c, sca_entity e, sca_partition p WHERE c.composite_sca_entity_id =
  AND rownum <= batch_size;
   DBMS_OUTPUT.PUT_LINE ('Total purgeable flow instance: ' ||  purgeable_instance);

Add Your Comment