I have not gone through and tested this myself, but it looks pretty valid. So proceed at your own risk (but Marc is top-notch, so you'll be fine). The script below is of a slightly different format from what you'll find on Marc's blog, but the content is identical.
REM
REM AUTHOR: http://orasoa.blogspot.com/2008/03/purging-esb-instances.html
REM
REM SCRIPT NAME: purge_esb_instances.sql
REM
REM DESCRIPTION: This script purges ESB instance data from the dehydration store
REM
REM EXAMPLE: sqlplus oraesb/pwd@database
REM EXEC purge_esb_instances(10); -- to purge 10 days worth of data
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;
/
REM AUTHOR: http://orasoa.blogspot.com/2008/03/purging-esb-instances.html
REM
REM SCRIPT NAME: purge_esb_instances.sql
REM
REM DESCRIPTION: This script purges ESB instance data from the dehydration store
REM
REM EXAMPLE: sqlplus oraesb/pwd@database
REM EXEC purge_esb_instances(10); -- to purge 10 days worth of data
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;
/
No comments:
Post a Comment