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:
REM
REM Name:
REM
REM purge_esb_instances.sql
REM
REM Description:
REM
REM This script purges data from the BPEL dehydration store
REM
REM Usage:
REM
REM purge_esb_instances
REM
REM p_older_than DEFAULT 999 - delete instances older than # days
REM
REM Example:
REM
REM sqlplus oraesb/pwd@database
REM exec purge_esb_instances(10);
REM commit;
REM
CREATE OR REPLACE
PROCEDURE purge_esb_instances
(
p_older_than IN NUMBER DEFAULT '999'
)
IS
diff NUMBER;
BEGIN
diff := (
(TRUNC(SYSDATE)-p_older_than) - TO_DATE('01/01/1970','MM/DD/YYYY')
)*24*60*60*1000;
DELETE ESB_ACTIVITY
WHERE ID IN
(
SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
FROM ESB_ACTIVITY B
where TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
DELETE ESB_TRACKING_FIELD_VALUE
WHERE ACTIVITY_ID IN
(
SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
from ESB_ACTIVITY B
WHERE TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
DELETE FROM ESB_FAULTED_INSTANCE
WHERE ACTIVITY_ID IN
(
SELECT ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
DELETE FROM ESB_TRANSACTION_STATUS
WHERE TIMESTAMP < diff;
--
DELETE FROM ESB_INSTANCE_RELATION_XML
WHERE FLOW_ID IN
(
SELECT FLOW_ID
FROM ESB_ACTIVITY A
WHERE EXISTS
(
SELECT FLOW_ID
FROM ESB_ACTIVITY B
WHERE TIMESTAMP < diff
AND A.FLOW_ID = B.FLOW_ID
)
);
--
IF(SQL%ROWCOUNT >0)
THEN
DELETE FROM ESB_RELATION_XML A
WHERE NOT EXISTS
(
SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML
);
--
DELETE ESB_SERVICE_RELATION
WHERE RELATION_XML_ID IN
(
SELECT ID
FROM ESB_RELATION_XML
WHERE NOT EXISTS
(
SELECT RELATION_XML_ID
FROM ESB_INSTANCE_RELATION_XML
WHERE ID = RELATION_XML_ID
)
);
END IF;
END;
/
SHOW ERRORS
