Delete BPEL instances

Running a production environment generates a huge amount of BPEL instances. Unless you have specified that you do not use dehydration :-).

It is possible to purge the instances all at once, as described in the article here (It also describes how to reclaim your space). But this purges all the instances. This is nice during development. But in a production environment you want to have a controlled way. A nice way to do this is via good-old PLSQL. Use the package COLLAXA in the ORABPEL schema.

This package has some nice methods to call:

procedure delete_txs
Deletes all the transactions that belong to a particular cube instance.

procedure delete_ci
Deletes a cube instance and all rows in other Collaxa tables that reference the cube instance. Since we don't have referential integrity on the tables (for performance reasons), we need this method to help clean up the database easily.

You can make a query to select the BPEL instances to delete and call the package:

create or replace
PROCEDURE purge_instances
(
p_domain       IN VARCHAR2 DEFAULT '%'
, p_process_name IN VARCHAR2 DEFAULT '%'
, p_revision     IN VARCHAR2 DEFAULT '%'
, p_older_than   IN NUMBER   DEFAULT 999
)
IS
CURSOR c
(
b_domain       IN VARCHAR2
, b_process_name IN VARCHAR2
, b_revision     IN VARCHAR2
, b_older_than   IN VARCHAR2
)
IS
SELECT
cie.cikey           cikey
, dmn.domain_id       domain_id
, cie.process_id      process_id
, cie.revision_tag    revision_tag
, cie.modify_date     modify_date
, cie.domain_ref      domain_ref
FROM
cube_instance cie
, domain        dmn
WHERE cie.domain_ref = dmn.domain_ref
--
-- the name of the domain
AND dmn.domain_id    LIKE b_domain
--
-- code 5 means completed
AND cie.modify_date  < TRUNC(sysdate)-b_older_than
--
AND cie.process_id   LIKE b_process_name
AND cie.revision_tag LIKE b_revision;
BEGIN
FOR r in c
(
 p_domain
, p_process_name
, p_revision
, p_older_than
)
LOOP
 DBMS_OUTPUT.PUT_LINE
 (
   'Purge '||r.process_id||'('||r.revision_tag||')'||
   ' at '||to_char(r.modify_date, 'YYYY-MM-DD HH24:MI:SS')
 );
 collaxa.DELETE_CI(r.cikey);
--
 delete from wftask wfn where wfn.instanceid = r.cikey;
 --
END LOOP;
--
-- delete invoke calls
-- invoked messages
DELETE FROM invoke_message_bin imn
WHERE EXISTS
(
 SELECT 1
 FROM  invoke_message ime
 , domain        dmn
 WHERE ime.message_guid = imn.message_guid
 AND   ime.domain_ref = dmn.domain_ref
 AND   dmn.domain_id    LIKE p_domain
 AND   ime.state        > 1
 AND   ime.process_id   LIKE  p_process_name
 AND   ime.revision_tag LIKE  p_revision
 AND   ime.receive_date < TRUNC(sysdate)-p_older_than
);
--
DELETE FROM invoke_message ime
WHERE  ime.domain_ref in
(
 SELECT dmn.DOMAIN_REF
 from   domain        dmn
 WHERE  dmn.domain_id    LIKE p_domain
)
AND   ime.state        > 1
AND   ime.process_id   LIKE  p_process_name
AND   ime.revision_tag LIKE  p_revision
AND   ime.receive_date < TRUNC(sysdate)-p_older_than;
--
DBMS_OUTPUT.PUT_LINE ('-> #invoke msg '||SQL%ROWCOUNT);
--
--
-- delete callback calls
DELETE FROM dlv_message_bin dmb
WHERE EXISTS
(
 SELECT 1
 FROM  dlv_message dme
 , domain        dmn
 WHERE dme.message_guid = dmb.message_guid
 AND   dme.domain_ref = dmn.domain_ref
 AND   dmn.domain_id    LIKE p_domain
 AND   dme.state        > 1
 AND   dme.process_id   LIKE  p_process_name
 AND   dme.revision_tag LIKE  p_revision
 AND   dme.receive_date < TRUNC(sysdate)-p_older_than
);
--
DELETE FROM dlv_message dme
WHERE  dme.domain_ref IN
(
 SELECT dmn.DOMAIN_REF
 from   domain        dmn
 WHERE  dmn.domain_id  LIKE p_domain
)
AND   dme.state        > 1
AND   dme.process_id   LIKE  p_process_name
AND   dme.revision_tag LIKE  p_revision
AND   dme.receive_date < TRUNC(sysdate)-p_older_than;
--
DBMS_OUTPUT.PUT_LINE ('-> #callback msg '||SQL%ROWCOUNT);
END;
/