Here is a script that can be used to purge Oracle ESB instances from the ORAESB schema. I created the script that it can be used by calling a Pl/SQL procedure, to delete instances older than a 'x' days; for a particular bpel process ; version and domain:
REM
REM Name:
REM
REM purge_bpel_dehydration.sql
REM
REM Description:
REM
REM This script purges data from the BPEL dehydration store
REM
REM Usage:
REM
REM purge_bpel_dehydration
REM
REM p_domain DEFAULT '%' - the name of the BPEL domain
REM p_process_name DEFAULT '%' - the name of the BPEL process
REM p_revision DEFAULT '%' - the version of the BPEL process
REM p_older_than DEFAULT 999 - delete instances older than # days
REM p_state DEFAULT -1 - the state of the process
REM
REM Example:
REM
REM sqlplus orabpel/pwd@database
REM exec purge_bpel_dehydration(10);
REM commit;
REM
CREATE OR REPLACE
PROCEDURE purge_bpel_dehydration
(
p_domain IN VARCHAR2 DEFAULT '%'
, p_process_name IN VARCHAR2 DEFAULT '%'
, p_revision IN VARCHAR2 DEFAULT '%'
, p_older_than IN NUMBER DEFAULT 999
, p_state IN NUMBER DEFAULT -1
)
IS
CURSOR c
(
b_domain IN VARCHAR2
, b_process_name IN VARCHAR2
, b_revision IN VARCHAR2
, b_older_than IN NUMBER
, b_state1 IN NUMBER
, b_state2 IN NUMBER
)
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
, cie.state process_status
FROM
cube_instance cie
, domain dmn
WHERE cie.domain_ref = dmn.domain_ref
AND cie.state >= b_state1
AND cie.state <= b_state2
--
-- 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;
l_state1 number;
l_state2 number;
BEGIN
IF p_state = -1
THEN
l_state1 := -1;
l_state2 := 999;
ELSE
l_state1 := p_state;
l_state2 := p_state;
END IF;
FOR r in c
(
p_domain
, p_process_name
, p_revision
, p_older_than
, l_state1
, l_state2
)
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 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 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;
/
SHOW ERRORS
