OracleBpelPurgeTroubleshooting.sql

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