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;
/
