Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
--Change this variable for your purge date value. In this case the Bpel purge purge all instance older than &purgeDays days
DECLARE purgeDays number := 72;
--TroubleShooting BPEL Purge
--Not purged instances
SELECT
ci.ecid AS ecid,
decode(ci.state,0, 'STATE_INITIATED(NOK)',1,'STATE_OPEN_RUNNING(OK)',2,'STATE_OPEN_SUSPENDED(OK)',3,'STATE_OPEN_FAULTED(OK)',4,'STATE_CLOSED_PENDING_CANCEL(OK)',5,'STATE_CLOSED_COMPLETED(NOK)',6,'STATE_CLOSED_FAULTED(NOK)', 7, 'STATE_CLOSED_CANCELLED(NOK)', 8, 'STATE_CLOSED_ABORTED(NOK)', 9, 'STATE_CLOSED_STALE(NOK)', 10, 'STATE_CLOSED_ROLLED_BACK(NOK)') AS state,
ci.composite_name AS composite_name,
ci.composite_revision AS composite_revision,
TO_CHAR(ci.creation_date at TIME ZONE(tz_offset('Europe/Paris')), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS creation_time,
TO_CHAR(ci.modify_date at TIME ZONE(tz_offset('Europe/Paris')), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS modify_date
FROM
YOUR_SOA_USER.cube_instance ci
where
ci.modify_date <= (sysdate-&purgeDays)
order by ci.modify_date desc;
--not purged DLVMessage by types
SELECT
dv.ecid AS ecid,
dv.composite_name AS composite_name,
dv.cikey,
decode(dv.state,0,'STATE_UNRESOLVED',1,'STATE_RESOLVED',2,'STATE_HANDLED',3,'STATE_CANCELLED',4,'STATE_MAX_RECOVERED') AS dlv_state,
decode(dv.dlv_type,1,'Invoke',2,'Callback') AS dlv_type
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (sysdate-&purgeDays)
group by dv.ecid, dv.composite_name, dv.cikey, dv.state, dv.dlv_type;
-- count not purged DLVMessages
select
count(dv.ecid)
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (sysdate-&purgeDays);
--
select count (task.ecid)
from YOUR_SOA_USER.wftask task
where task.state IS NOT NULL AND
task.state NOT IN ('DELETED','ERRORED','EXPIRED','STALE','WITHDRAWN');
SELECT unique mi.ECID from YOUR_SOA_USER.MEDIATOR_INSTANCE mi
where mi.component_state between 4 and 15;
SELECT dlv.ECID from YOUR_SOA_USER.DLV_MESSAGE dlv
WHERE dlv.dlv_type=1 and dlv.state in (0,1);
select unique ecid, state from YOUR_SOA_USER.composite_instance
where (bitand(state,127)=1 or bitand(state,6)=2 or bitand(state,16)=16 or
bitand(state,64)=64 or bitand(state,127)=32);
--Recoverable message not purgeable (6)
select *
from YOUR_SOA_USER.dlv_message dlv
inner join YOUR_SOA_USER.cube_instance ci on ci.ecid = dlv.ecid
where ci.state < 5
and ci.creation_date <= (sysdate-&purgeDays)
and ci.ecid=dlv.ecid
and dlv.dlv_type=1
AND dlv.STATE in (0,1);
--purgeable instance 394 059
select count(ci.ecid) from YOUR_SOA_USER.cube_instance ci
where ci.state >= 5
and ci.MODIFY_DATE <= (sysdate-&purgeDays);
-- Running cube instance records, NOT eligible for purging: 266 828
select count(ci.ecid)
from YOUR_SOA_USER.cube_instance ci
where ci.state < 5
and ci.MODIFY_DATE <= (sysdate-&purgeDays);
--not purged DLVMessage by types
SELECT
dv.composite_name AS composite_name,
decode(dv.state,0,'STATE_UNRESOLVED',1,'STATE_RESOLVED',2,'STATE_HANDLED',3,'STATE_CANCELLED',4,'STATE_MAX_RECOVERED') AS dlv_state,
decode(dv.dlv_type,1,'Invoke',2,'Callback') AS dlv_type
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (sysdate-&purgeDays)
group by dv.composite_name, dv.state, dv.dlv_type;
select
count(dv.ecid)
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (trunc(sysdate)-&purgeDays);
--All Older instances
select count(ci.ecid) from YOUR_SOA_USER.cube_instance ci
where ci.modify_date <= (trunc(sysdate)-&purgeDays);
--purgeable instance
select count(ci.ecid) from YOUR_SOA_USER.cube_instance ci
where ci.state >= 5
and ci.MODIFY_DATE <= (trunc(sysdate)-&purgeDays);
-- Running cube instance records, NOT eligible for purging:
select count(ci.ecid)
from YOUR_SOA_USER.cube_instance ci
where ci.state < 5
and ci.MODIFY_DATE <= (trunc(sysdate)-&purgeDays);
--If you need to force instance to a purgeable state here is how
--Force all old instance(>&purgeDays) to a purgeable state
update YOUR_SOA_USER.composite_instance set state=16 where created_time < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.work_item set state = 10, modify_date = sysdate where modify_date < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.cube_instance ci set ci.state = 8 ,
ci.modify_date = sysdate - 70 where ci.creation_date < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.dlv_message set state = 3 where receive_date < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.dlv_subscription set state = -1 where subscription_date < trunc(sysdate)-&purgeDays;
COMMIT;
--Delete unpurgeable element
delete from YOUR_SOA_USER.xml_document xd where doc_partition_date < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.instance_payload ip where created_time < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.headers_properties hp where modify_date < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.document_dlv_msg_ref ddmr where dlv_partition_date < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.dlv_message dm where receive_date < trunc(sysdate)-&purgeDays;
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment