This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jun 4, 2013 4:52 AM by 1008739 RSS

Unable to purge bpel instances

1008739 Newbie
Currently Being Moderated
Hello All,

We have upgraded our bpel server to 11.1.1.6 and using below purging sql for deletion.

DECLARE

MAX_CREATION_DATE timestamp;
MIN_CREATION_DATE timestamp;
batch_size integer;
max_runtime integer;
retention_period timestamp;

BEGIN

MIN_CREATION_DATE := to_timestamp('2013-01-01','YYYY-MM-DD');
MAX_CREATION_DATE := to_timestamp('2013-05-07','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2013-05-07','YYYY-MM-DD');
batch_size := 10000;
soa.delete_instances(
min_creation_date => MIN_CREATION_DATE,
max_creation_date => MAX_CREATION_DATE,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
purge_partitioned_component => false);
END;

Somehow the pl/sql is executing without error in 2 sec but the instances are not deleted from composite instance table. Iam not sure why it is not working.

Edited by: 1005736 on May 13, 2013 11:30 PM
  • 1. Re: Unable to purge bpel instances
    999231 Newbie
    Currently Being Moderated
    Hi
    Before run the purge script please run the following statements and make sure that records exist in respective tables.

    select count(*) from composite_instance
    select count(*) from cube_instance
    select count(*) from mediator_instance
    select count(*) from audit_trail
    select count(*) from xml_document

    Take the count and run the script. After that run the above statements and let me know if you find any differences.

    I have one more doubt that the dates(min and max) given in script may not exist in tables. please check once.


    Thanks
    Vamsi..

    Edited by: VenkataThota on May 14, 2013 12:21 AM

    Edited by: VenkataThota on May 14, 2013 12:22 AM
  • 2. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    Hello,

    Thanks for the response Vamsi. I have checked the data in below tables and found the below counts.

    select count(*) from composite_instance 381
    select count(*) from cube_instance 381
    select count(*) from mediator_instance 0
    select count(*) from audit_trail 855
    select count(*) from xml_document 399

    I have changed the date in min creation time but still no result. I have checked the minimum creation date in the table and have used them in the below script many times but still no luck. The sql executed in 4 sec without deleting any instances.


    DECLARE

    MAX_CREATION_DATE timestamp;
    MIN_CREATION_DATE timestamp;
    batch_size integer;
    max_runtime integer;
    retention_period timestamp;

    BEGIN

    MIN_CREATION_DATE := to_timestamp('2013-04-14','YYYY-MM-DD');
    MAX_CREATION_DATE := to_timestamp('2013-04-15','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2013-04-16','YYYY-MM-DD');
    batch_size := 10000;
    soa.delete_instances(
    min_creation_date => MIN_CREATION_DATE,
    max_creation_date => MAX_CREATION_DATE,
    batch_size => batch_size,
    max_runtime => max_runtime,
    retention_period => retention_period,
    purge_partitioned_component => false);
    END;

    Edited by: 1005736 on May 14, 2013 1:06 AM

    Edited by: 1005736 on May 14, 2013 1:15 AM
  • 3. Re: Unable to purge bpel instances
    999231 Newbie
    Currently Being Moderated
    Hi
    Can you please check the Min and Max dates which you passed to the script.
    Run the following statement

    select max(creation_date),min(creation_date) from cube_instance;

    check your passing dates are exist in between the resulted dates of above query.

    Thanks
    Vamsi..
  • 4. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    Hi Vamsi,

    Yeah the date exist between the two.

    This is what i got after running the sql

    Session altered.

    MAX(CREATION_DATE) MIN(CREATION_DATE)
    ------------------------------- -------------------------------
    14-MAY-13 12.34.40.901000 AM 14-APR-13 09.32.13.039000 PM
    1 row selected.

    DECLARE

    MAX_CREATION_DATE timestamp;
    MIN_CREATION_DATE timestamp;
    batch_size integer;
    max_runtime integer;
    retention_period timestamp;

    BEGIN

    MIN_CREATION_DATE := to_timestamp('2013-04-14','YYYY-MM-DD');
    MAX_CREATION_DATE := to_timestamp('2013-05-14','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2013-05-15','YYYY-MM-DD');
    batch_size := 10000;
    soa.delete_instances(
    min_creation_date => MIN_CREATION_DATE,
    max_creation_date => MAX_CREATION_DATE,
    batch_size => batch_size,
    max_runtime => max_runtime,
    retention_period => retention_period,
    purge_partitioned_component => false);
    END;
  • 5. Re: Unable to purge bpel instances
    999231 Newbie
    Currently Being Moderated
    Hi
    Please try this as it is

    DECLARE
    min_creation_date cube_instance.creation_date%TYPE;
    max_creation_date cube_instance.creation_date%TYPE;     
    BEGIN
    SELECT MIN(creation_date)
    INTO min_creation_date
    FROM cube_instance;
    SELECT MAX(creation_date)
    INTO max_creation_date
    FROM cube_instance;

    soa.delete_instances (min_creation_date, max_creation_date);
    END;
    COMMIT;

    Thanks
    Vamsi..

    Edited by: VenkataThota on May 14, 2013 4:39 AM
  • 6. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    Hi Vamsi,

    Sorry to say but it didn't worked. It executed without errors in 2 sec.

    I have run the sql with the standard one in debug mode and this was the output.

    Session altered.
    Procedure altered.
    Procedure altered.
    06-MAY-2013 15:48:34 : procedure delete_instances
    06-MAY-2013 15:48:34 : time check
    06-MAY-2013 15:48:34 : sysdate = 06/MAY/2013:15/48
    06-MAY-2013 15:48:34 : stoptime = 06/MAY/2013:16/48
    06-MAY-2013 15:48:34 : checking for partitions
    06-MAY-2013 15:48:34 : done checking for partitions
    06-MAY-2013 15:48:34 : loop count = 1
    06-MAY-2013 15:48:34 : deleting non-orphaned instances
    06-MAY-2013 15:48:34 Number of rows in table ecid_purge Inserted = 5
    06-MAY-2013 15:48:34 : total rows before pruning 5
    06-MAY-2013 15:48:34 : calling pruneOpenECIDs
    06-MAY-2013 15:48:34 Number of rows in table prune_running_insts Inserted (bpel) for open ci 5
    06-MAY-2013 15:48:34 Number of rows in table prune_running_insts Inserted (bpel) for dlv_message0
    06-MAY-2013 15:48:34 Number of rows in table ecid_purge Deleted (bpel) for dlv_message5
    06-MAY-2013 15:48:34 Number of rows in table prune_running_insts Inserted 0
    06-MAY-2013 15:48:34 Number of rows in table ecid_purge purged is : 0
    06-MAY-2013 15:48:34 Number of rows in table prune_running_insts Workflow inserts into prune table = 0
    06-MAY-2013 15:48:34 Number of rows in table ecid_purge Workflow deletes ECIDs for open WFTask = 0
    06-MAY-2013 15:48:34 Number of rows in table prune_running_insts Inserted 0
    06-MAY-2013 15:48:34 Number of rows in table ecid_purge purged is : 0
    06-MAY-2013 15:48:34 : finished pruneOpenECIDs
    06-MAY-2013 15:48:34 : total_rows_after_pruning 0
    06-MAY-2013 15:48:34 : calling soa_orabpel.deleteComponentInstances
    06-MAY-2013 15:48:35 Number of rows in table temp_cube_instance Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table temp_document_ci_ref Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table temp_document_dlv_msg_ref Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table HEADERS_PROPERTIES purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AG_INSTANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table TEST_DETAILS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table CUBE_SCOPE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AUDIT_COUNTER purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AUDIT_TRAIL purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AUDIT_DETAILS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table CI_INDEXES purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table WORK_ITEM purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table WI_FAULT purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table XML_DOCUMENT purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DOCUMENT_DLV_MSG_REF purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DOCUMENT_CI_REF purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DLV_MESSAGE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DLV_SUBSCRIPTION purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table CUBE_INSTANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_AUDIT_QUERY purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_MEASUREMENT_ACTIONS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_MEASUREMENT_ACTION_EXCEPS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_CUBE_AUDITINSTANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_CUBE_TASKPERFORMANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_CUBE_PROCESSPERFORMANCE purged is : 0
    06-MAY-2013 15:48:35 : completed soa_orabpel.deleteComponentInstances
    06-MAY-2013 15:48:35 : calling workflow.deleteComponentInstances
    06-MAY-2013 15:48:35 : workflow.deleteComponentInstance begin
    06-MAY-2013 15:48:35 Number of rows in table temp_wftask_purge Inserted = 0
    06-MAY-2013 15:48:35 : Purging WFTask_TL
    06-MAY-2013 15:48:35 Number of rows in table WFTask_TL purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskHistory
    06-MAY-2013 15:48:35 Number of rows in table WFTaskHistory purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskHistory_TL
    06-MAY-2013 15:48:35 Number of rows in table WFTaskHistory_TL purged is : 0
    06-MAY-2013 15:48:35 : Purging WFComments
    06-MAY-2013 15:48:35 Number of rows in table WFComments purged is : 0
    06-MAY-2013 15:48:35 : Purging WFMessageAttribute
    06-MAY-2013 15:48:35 Number of rows in table WFMessageAttribute purged is : 0
    06-MAY-2013 15:48:35 : Purging WFAttachment
    06-MAY-2013 15:48:35 Number of rows in table WFAttachment purged is : 0
    06-MAY-2013 15:48:35 : Purging WFAssignee
    06-MAY-2013 15:48:35 Number of rows in table WFAssignee purged is : 0
    06-MAY-2013 15:48:35 : Purging WFReviewer
    06-MAY-2013 15:48:35 Number of rows in table WFReviewer purged is : 0
    06-MAY-2013 15:48:35 : Purging WFCollectionTarget
    06-MAY-2013 15:48:35 Number of rows in table WFCollectionTarget purged is : 0
    06-MAY-2013 15:48:35 : Purging WFRoutingSlip
    06-MAY-2013 15:48:35 Number of rows in table WFRoutingSlip purged is : 0
    06-MAY-2013 15:48:35 : Purging WFNotification
    06-MAY-2013 15:48:35 Number of rows in table WFNotification purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskTimer
    06-MAY-2013 15:48:35 Number of rows in table WFTaskTimer purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskError
    06-MAY-2013 15:48:35 Number of rows in table WFTaskError purged is : 0
    06-MAY-2013 15:48:35 : Purging WFHeaderProps
    06-MAY-2013 15:48:35 Number of rows in table WFHeaderProps purged is : 0
    06-MAY-2013 15:48:35 : Purging WFEvidence
    06-MAY-2013 15:48:35 Number of rows in table WFEvidence purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskAssignmentStatistic
    06-MAY-2013 15:48:35 Number of rows in table WFTaskAssignmentStatistic purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskAggregation
    06-MAY-2013 15:48:35 Number of rows in table WFTaskAggregation purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTask
    06-MAY-2013 15:48:35 Number of rows in table WFTask purged is : 0
    06-MAY-2013 15:48:35 : workflow.deleteComponentInstance end
    06-MAY-2013 15:48:35 : completed workflow.deleteComponentInstances
    06-MAY-2013 15:48:35 : calling mediator.deleteComponentInstances
    06-MAY-2013 15:48:35 Number of rows in table temp_mediator_instance Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table mediator_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table mediator_deferred_message purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table mediator_document purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table mediator_case_detail purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table mediator_case_instance purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table mediator_instance purged is : 0
    06-MAY-2013 15:48:35 : completed mediator.deleteComponentInstances
    06-MAY-2013 15:48:35 : calling decision.deleteComponentInstances
    06-MAY-2013 15:48:35 Number of rows in table temp_brdecision_instance Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table BRDecisionFault purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BRDecisionUnitOfWork purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BRDecisonInstance purged is : 0
    06-MAY-2013 15:48:35 : completed decision.deleteComponentInstances
    06-MAY-2013 15:48:35 : calling fabric.deleteComponentInstances
    06-MAY-2013 15:48:35 Number of rows in table reference_instance_purge inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table reference_instance purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table rejected_msg_native_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table composite_instance_fault purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table composite_sensor_value purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table composite_instance_assoc purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table component_instance purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table attachment purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table attachment_ref purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table composite_instance purged is : 0
    06-MAY-2013 15:48:35 : completed fabric.deleteComponentInstances
    06-MAY-2013 15:48:35 : time check
    06-MAY-2013 15:48:35 : sysdate = 06/MAY/2013:15/48
    06-MAY-2013 15:48:35 : stoptime = 06/MAY/2013:16/48
    06-MAY-2013 15:48:35 : loop count = 2
    06-MAY-2013 15:48:35 : deleting orphaned instances
    06-MAY-2013 15:48:35 : calling soa_orabpel.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 Number of rows in table temp_document_dlv_msg_ref Inserted no cikey 0
    06-MAY-2013 15:48:35 : No BPEL instances found with null composite instance ids
    06-MAY-2013 15:48:35 : completed soa_orabpel.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 : calling workflow.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 : workflow.deleteNoCompositeIdInstances begin
    06-MAY-2013 15:48:35 : workflow.deleteNoCompositeIdInstances done. No WFTask instances were found with ecId equals null
    06-MAY-2013 15:48:35 : completed workflow.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 : calling mediator.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 Number of rows in table temp_mediator_instance Inserted = 0
    06-MAY-2013 15:48:35 : No Mediator instances found with composite instance id as null or zero
    06-MAY-2013 15:48:35 : completed mediator.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 : calling decision.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 Number of rows in table temp_brdecision_instance Inserted = 0
    06-MAY-2013 15:48:35 : No Decision instances found with null composite instance ids
    06-MAY-2013 15:48:35 : completed decision.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 : calling fabric.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 Number of rows in table reference_instance_purge inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table reference_instance purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table composite_fault_purge inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table rejected_msg_native_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table composite_instance_fault purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table component_instance purged is : 0
    06-MAY-2013 15:48:35 : completed fabric.deleteNoCompositeIdInstances
    06-MAY-2013 15:48:35 : time check
    06-MAY-2013 15:48:35 : sysdate = 06/MAY/2013:15/48
    06-MAY-2013 15:48:35 : stoptime = 06/MAY/2013:16/48
    06-MAY-2013 15:48:35 : loop count = 3
    06-MAY-2013 15:48:35 : deleting non-orphaned instances
    06-MAY-2013 15:48:35 Number of rows in table ecid_purge Inserted = 0
    06-MAY-2013 15:48:35 : total rows before pruning 0
    06-MAY-2013 15:48:35 : calling pruneOpenECIDs
    06-MAY-2013 15:48:35 Number of rows in table prune_running_insts Inserted (bpel) for open ci 0
    06-MAY-2013 15:48:35 Number of rows in table prune_running_insts Inserted (bpel) for dlv_message0
    06-MAY-2013 15:48:35 Number of rows in table ecid_purge Deleted (bpel) for dlv_message0
    06-MAY-2013 15:48:35 Number of rows in table prune_running_insts Inserted 0
    06-MAY-2013 15:48:35 Number of rows in table ecid_purge purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table prune_running_insts Workflow inserts into prune table = 0
    06-MAY-2013 15:48:35 Number of rows in table ecid_purge Workflow deletes ECIDs for open WFTask = 0
    06-MAY-2013 15:48:35 Number of rows in table prune_running_insts Inserted 0
    06-MAY-2013 15:48:35 Number of rows in table ecid_purge purged is : 0
    06-MAY-2013 15:48:35 : finished pruneOpenECIDs
    06-MAY-2013 15:48:35 : total_rows_after_pruning 0
    06-MAY-2013 15:48:35 : calling soa_orabpel.deleteComponentInstances
    06-MAY-2013 15:48:35 Number of rows in table temp_cube_instance Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table temp_document_ci_ref Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table temp_document_dlv_msg_ref Inserted = 0
    06-MAY-2013 15:48:35 Number of rows in table HEADERS_PROPERTIES purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AG_INSTANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table TEST_DETAILS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table CUBE_SCOPE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AUDIT_COUNTER purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AUDIT_TRAIL purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table AUDIT_DETAILS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table CI_INDEXES purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table WORK_ITEM purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table WI_FAULT purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table XML_DOCUMENT purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DOCUMENT_DLV_MSG_REF purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DOCUMENT_CI_REF purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DLV_MESSAGE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table DLV_SUBSCRIPTION purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table CUBE_INSTANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_AUDIT_QUERY purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_MEASUREMENT_ACTIONS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_MEASUREMENT_ACTION_EXCEPS purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_CUBE_AUDITINSTANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_CUBE_TASKPERFORMANCE purged is : 0
    06-MAY-2013 15:48:35 Number of rows in table BPM_CUBE_PROCESSPERFORMANCE purged is : 0
    06-MAY-2013 15:48:35 : completed soa_orabpel.deleteComponentInstances
    06-MAY-2013 15:48:35 : calling workflow.deleteComponentInstances
    06-MAY-2013 15:48:35 : workflow.deleteComponentInstance begin
    06-MAY-2013 15:48:35 Number of rows in table temp_wftask_purge Inserted = 0
    06-MAY-2013 15:48:35 : Purging WFTask_TL
    06-MAY-2013 15:48:35 Number of rows in table WFTask_TL purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskHistory
    06-MAY-2013 15:48:35 Number of rows in table WFTaskHistory purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskHistory_TL
    06-MAY-2013 15:48:35 Number of rows in table WFTaskHistory_TL purged is : 0
    06-MAY-2013 15:48:35 : Purging WFComments
    06-MAY-2013 15:48:35 Number of rows in table WFComments purged is : 0
    06-MAY-2013 15:48:35 : Purging WFMessageAttribute
    06-MAY-2013 15:48:35 Number of rows in table WFMessageAttribute purged is : 0
    06-MAY-2013 15:48:35 : Purging WFAttachment
    06-MAY-2013 15:48:35 Number of rows in table WFAttachment purged is : 0
    06-MAY-2013 15:48:35 : Purging WFAssignee
    06-MAY-2013 15:48:35 Number of rows in table WFAssignee purged is : 0
    06-MAY-2013 15:48:35 : Purging WFReviewer
    06-MAY-2013 15:48:35 Number of rows in table WFReviewer purged is : 0
    06-MAY-2013 15:48:35 : Purging WFCollectionTarget
    06-MAY-2013 15:48:35 Number of rows in table WFCollectionTarget purged is : 0
    06-MAY-2013 15:48:35 : Purging WFRoutingSlip
    06-MAY-2013 15:48:35 Number of rows in table WFRoutingSlip purged is : 0
    06-MAY-2013 15:48:35 : Purging WFNotification
    06-MAY-2013 15:48:35 Number of rows in table WFNotification purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskTimer
    06-MAY-2013 15:48:35 Number of rows in table WFTaskTimer purged is : 0
    06-MAY-2013 15:48:35 : Purging WFTaskError
    06-MAY-2013 15:48:36 Number of rows in table WFTaskError purged is : 0
    06-MAY-2013 15:48:36 : Purging WFHeaderProps
    06-MAY-2013 15:48:36 Number of rows in table WFHeaderProps purged is : 0
    06-MAY-2013 15:48:36 : Purging WFEvidence
    06-MAY-2013 15:48:36 Number of rows in table WFEvidence purged is : 0
    06-MAY-2013 15:48:36 : Purging WFTaskAssignmentStatistic
    06-MAY-2013 15:48:36 Number of rows in table WFTaskAssignmentStatistic purged is : 0
    06-MAY-2013 15:48:36 : Purging WFTaskAggregation
    06-MAY-2013 15:48:36 Number of rows in table WFTaskAggregation purged is : 0
    06-MAY-2013 15:48:36 : Purging WFTask
    06-MAY-2013 15:48:36 Number of rows in table WFTask purged is : 0
    06-MAY-2013 15:48:36 : workflow.deleteComponentInstance end
    06-MAY-2013 15:48:36 : completed workflow.deleteComponentInstances
    06-MAY-2013 15:48:36 : calling mediator.deleteComponentInstances
    06-MAY-2013 15:48:36 Number of rows in table temp_mediator_instance Inserted = 0
    06-MAY-2013 15:48:36 Number of rows in table mediator_payload purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table mediator_deferred_message purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table mediator_document purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table mediator_case_detail purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table mediator_case_instance purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table mediator_instance purged is : 0
    06-MAY-2013 15:48:36 : completed mediator.deleteComponentInstances
    06-MAY-2013 15:48:36 : calling decision.deleteComponentInstances
    06-MAY-2013 15:48:36 Number of rows in table temp_brdecision_instance Inserted = 0
    06-MAY-2013 15:48:36 Number of rows in table BRDecisionFault purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table BRDecisionUnitOfWork purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table BRDecisonInstance purged is : 0
    06-MAY-2013 15:48:36 : completed decision.deleteComponentInstances
    06-MAY-2013 15:48:36 : calling fabric.deleteComponentInstances
    06-MAY-2013 15:48:36 Number of rows in table reference_instance_purge inserted = 0
    06-MAY-2013 15:48:36 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table reference_instance purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table rejected_msg_native_payload purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table composite_instance_fault purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table composite_sensor_value purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table composite_instance_assoc purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table component_instance purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table attachment purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table attachment_ref purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table composite_instance purged is : 0
    06-MAY-2013 15:48:36 : completed fabric.deleteComponentInstances
    06-MAY-2013 15:48:36 : time check
    06-MAY-2013 15:48:36 : sysdate = 06/MAY/2013:15/48
    06-MAY-2013 15:48:36 : stoptime = 06/MAY/2013:16/48
    06-MAY-2013 15:48:36 Number of rows in table rejected_message_purge inserted = 0
    06-MAY-2013 15:48:36 Number of rows in table xml_document purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table rejected_msg_native_payload purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table instance_payload purged is : 0
    06-MAY-2013 15:48:36 Number of rows in table rejected_message purged is : 0
    06-MAY-2013 15:48:36 : delete_instances completed successfully
    PL/SQL procedure successfully completed.

    Thanks
    Vikram
  • 7. Re: Unable to purge bpel instances
    999231 Newbie
    Currently Being Moderated
    Hi Vikram
    In ur debug log sysdate is showing

    06-MAY-2013 15:48:34 : sysdate = 06/MAY/2013:15/48
    06-MAY-2013 15:48:34 : stoptime = 06/MAY/2013:16/48

    Which OS ur using??
    If ur using Windows change the date to todays date and restart the DB and run the script which i sent in before post.

    Thanks
    Vamsi..
  • 8. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    Oh Iam sorry actually this is the one i ran few days back and this is for one environment having 5 bpel instances.

    Iam working on Linux (redhat).
  • 9. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    And this is what i got after running your sql script few minutes back

    Session altered.
    Procedure altered.
    Procedure altered.
    14-MAY-2013 06:05:21 : procedure delete_instances
    14-MAY-2013 06:05:21 : time check
    14-MAY-2013 06:05:21 : sysdate = 14/MAY/2013:06/05
    14-MAY-2013 06:05:21 : stoptime = 14/MAY/2013:07/05
    14-MAY-2013 06:05:21 : checking for partitions
    14-MAY-2013 06:05:21 : done checking for partitions
    14-MAY-2013 06:05:21 : loop count = 1
    14-MAY-2013 06:05:21 : deleting non-orphaned instances
    14-MAY-2013 06:05:21 Number of rows in table ecid_purge Inserted = 377
    14-MAY-2013 06:05:21 : total rows before pruning 377
    14-MAY-2013 06:05:21 : calling pruneOpenECIDs
    14-MAY-2013 06:05:21 Number of rows in table prune_running_insts Inserted (bpel) for open ci 377
    14-MAY-2013 06:05:21 Number of rows in table prune_running_insts Inserted (bpel) for dlv_message0
    14-MAY-2013 06:05:21 Number of rows in table ecid_purge Deleted (bpel) for dlv_message377
    14-MAY-2013 06:05:21 Number of rows in table prune_running_insts Inserted 0
    14-MAY-2013 06:05:21 Number of rows in table ecid_purge purged is : 0
    14-MAY-2013 06:05:21 Number of rows in table prune_running_insts Workflow inserts into prune table = 0
    14-MAY-2013 06:05:21 Number of rows in table ecid_purge Workflow deletes ECIDs for open WFTask = 0
    14-MAY-2013 06:05:21 Number of rows in table prune_running_insts Inserted 0
    14-MAY-2013 06:05:21 Number of rows in table ecid_purge purged is : 0
    14-MAY-2013 06:05:21 : finished pruneOpenECIDs
    14-MAY-2013 06:05:21 : total_rows_after_pruning 0
    14-MAY-2013 06:05:21 : calling soa_orabpel.deleteComponentInstances
    14-MAY-2013 06:05:22 Number of rows in table temp_cube_instance Inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table temp_document_ci_ref Inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table temp_document_dlv_msg_ref Inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table HEADERS_PROPERTIES purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table AG_INSTANCE purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table TEST_DETAILS purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table CUBE_SCOPE purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table AUDIT_COUNTER purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table AUDIT_TRAIL purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table AUDIT_DETAILS purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table CI_INDEXES purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table WORK_ITEM purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table WI_FAULT purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table XML_DOCUMENT purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table DOCUMENT_DLV_MSG_REF purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table DOCUMENT_CI_REF purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table DLV_MESSAGE purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table DLV_SUBSCRIPTION purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table CUBE_INSTANCE purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BPM_AUDIT_QUERY purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BPM_MEASUREMENT_ACTIONS purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BPM_MEASUREMENT_ACTION_EXCEPS purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BPM_CUBE_AUDITINSTANCE purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BPM_CUBE_TASKPERFORMANCE purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BPM_CUBE_PROCESSPERFORMANCE purged is : 0
    14-MAY-2013 06:05:22 : completed soa_orabpel.deleteComponentInstances
    14-MAY-2013 06:05:22 : calling workflow.deleteComponentInstances
    14-MAY-2013 06:05:22 : workflow.deleteComponentInstance begin
    14-MAY-2013 06:05:22 Number of rows in table temp_wftask_purge Inserted = 0
    14-MAY-2013 06:05:22 : Purging WFTask_TL
    14-MAY-2013 06:05:22 Number of rows in table WFTask_TL purged is : 0
    14-MAY-2013 06:05:22 : Purging WFTaskHistory
    14-MAY-2013 06:05:22 Number of rows in table WFTaskHistory purged is : 0
    14-MAY-2013 06:05:22 : Purging WFTaskHistory_TL
    14-MAY-2013 06:05:22 Number of rows in table WFTaskHistory_TL purged is : 0
    14-MAY-2013 06:05:22 : Purging WFComments
    14-MAY-2013 06:05:22 Number of rows in table WFComments purged is : 0
    14-MAY-2013 06:05:22 : Purging WFMessageAttribute
    14-MAY-2013 06:05:22 Number of rows in table WFMessageAttribute purged is : 0
    14-MAY-2013 06:05:22 : Purging WFAttachment
    14-MAY-2013 06:05:22 Number of rows in table WFAttachment purged is : 0
    14-MAY-2013 06:05:22 : Purging WFAssignee
    14-MAY-2013 06:05:22 Number of rows in table WFAssignee purged is : 0
    14-MAY-2013 06:05:22 : Purging WFReviewer
    14-MAY-2013 06:05:22 Number of rows in table WFReviewer purged is : 0
    14-MAY-2013 06:05:22 : Purging WFCollectionTarget
    14-MAY-2013 06:05:22 Number of rows in table WFCollectionTarget purged is : 0
    14-MAY-2013 06:05:22 : Purging WFRoutingSlip
    14-MAY-2013 06:05:22 Number of rows in table WFRoutingSlip purged is : 0
    14-MAY-2013 06:05:22 : Purging WFNotification
    14-MAY-2013 06:05:22 Number of rows in table WFNotification purged is : 0
    14-MAY-2013 06:05:22 : Purging WFTaskTimer
    14-MAY-2013 06:05:22 Number of rows in table WFTaskTimer purged is : 0
    14-MAY-2013 06:05:22 : Purging WFTaskError
    14-MAY-2013 06:05:22 Number of rows in table WFTaskError purged is : 0
    14-MAY-2013 06:05:22 : Purging WFHeaderProps
    14-MAY-2013 06:05:22 Number of rows in table WFHeaderProps purged is : 0
    14-MAY-2013 06:05:22 : Purging WFEvidence
    14-MAY-2013 06:05:22 Number of rows in table WFEvidence purged is : 0
    14-MAY-2013 06:05:22 : Purging WFTaskAssignmentStatistic
    14-MAY-2013 06:05:22 Number of rows in table WFTaskAssignmentStatistic purged is : 0
    14-MAY-2013 06:05:22 : Purging WFTaskAggregation
    14-MAY-2013 06:05:22 Number of rows in table WFTaskAggregation purged is : 0
    14-MAY-2013 06:05:22 : Purging WFTask
    14-MAY-2013 06:05:22 Number of rows in table WFTask purged is : 0
    14-MAY-2013 06:05:22 : workflow.deleteComponentInstance end
    14-MAY-2013 06:05:22 : completed workflow.deleteComponentInstances
    14-MAY-2013 06:05:22 : calling mediator.deleteComponentInstances
    14-MAY-2013 06:05:22 Number of rows in table temp_mediator_instance Inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table mediator_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table mediator_deferred_message purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table mediator_document purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table mediator_case_detail purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table mediator_case_instance purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table mediator_instance purged is : 0
    14-MAY-2013 06:05:22 : completed mediator.deleteComponentInstances
    14-MAY-2013 06:05:22 : calling decision.deleteComponentInstances
    14-MAY-2013 06:05:22 Number of rows in table temp_brdecision_instance Inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table BRDecisionFault purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BRDecisionUnitOfWork purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table BRDecisonInstance purged is : 0
    14-MAY-2013 06:05:22 : completed decision.deleteComponentInstances
    14-MAY-2013 06:05:22 : calling fabric.deleteComponentInstances
    14-MAY-2013 06:05:22 Number of rows in table reference_instance_purge inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table reference_instance purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table rejected_msg_native_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table composite_instance_fault purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table composite_sensor_value purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table composite_instance_assoc purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table component_instance purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table attachment purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table attachment_ref purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table composite_instance purged is : 0
    14-MAY-2013 06:05:22 : completed fabric.deleteComponentInstances
    14-MAY-2013 06:05:22 : time check
    14-MAY-2013 06:05:22 : sysdate = 14/MAY/2013:06/05
    14-MAY-2013 06:05:22 : stoptime = 14/MAY/2013:07/05
    14-MAY-2013 06:05:22 : loop count = 2
    14-MAY-2013 06:05:22 : deleting orphaned instances
    14-MAY-2013 06:05:22 : calling soa_orabpel.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 Number of rows in table temp_document_dlv_msg_ref Inserted no cikey 0
    14-MAY-2013 06:05:22 : No BPEL instances found with null composite instance ids
    14-MAY-2013 06:05:22 : completed soa_orabpel.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 : calling workflow.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 : workflow.deleteNoCompositeIdInstances begin
    14-MAY-2013 06:05:22 : workflow.deleteNoCompositeIdInstances done. No WFTask instances were found with ecId equals null
    14-MAY-2013 06:05:22 : completed workflow.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 : calling mediator.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 Number of rows in table temp_mediator_instance Inserted = 0
    14-MAY-2013 06:05:22 : No Mediator instances found with composite instance id as null or zero
    14-MAY-2013 06:05:22 : completed mediator.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 : calling decision.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 Number of rows in table temp_brdecision_instance Inserted = 0
    14-MAY-2013 06:05:22 : No Decision instances found with null composite instance ids
    14-MAY-2013 06:05:22 : completed decision.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 : calling fabric.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 Number of rows in table reference_instance_purge inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table reference_instance purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table composite_fault_purge inserted = 0
    14-MAY-2013 06:05:22 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table rejected_msg_native_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table composite_instance_fault purged is : 0
    14-MAY-2013 06:05:22 Number of rows in table component_instance purged is : 0
    14-MAY-2013 06:05:22 : completed fabric.deleteNoCompositeIdInstances
    14-MAY-2013 06:05:22 : time check
    14-MAY-2013 06:05:22 : sysdate = 14/MAY/2013:06/05
    14-MAY-2013 06:05:22 : stoptime = 14/MAY/2013:07/05
    14-MAY-2013 06:05:22 : loop count = 3
    14-MAY-2013 06:05:22 : deleting non-orphaned instances
    14-MAY-2013 06:05:23 Number of rows in table ecid_purge Inserted = 0
    14-MAY-2013 06:05:23 : total rows before pruning 0
    14-MAY-2013 06:05:23 : calling pruneOpenECIDs
    14-MAY-2013 06:05:23 Number of rows in table prune_running_insts Inserted (bpel) for open ci 0
    14-MAY-2013 06:05:23 Number of rows in table prune_running_insts Inserted (bpel) for dlv_message0
    14-MAY-2013 06:05:23 Number of rows in table ecid_purge Deleted (bpel) for dlv_message0
    14-MAY-2013 06:05:23 Number of rows in table prune_running_insts Inserted 0
    14-MAY-2013 06:05:23 Number of rows in table ecid_purge purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table prune_running_insts Workflow inserts into prune table = 0
    14-MAY-2013 06:05:23 Number of rows in table ecid_purge Workflow deletes ECIDs for open WFTask = 0
    14-MAY-2013 06:05:23 Number of rows in table prune_running_insts Inserted 0
    14-MAY-2013 06:05:23 Number of rows in table ecid_purge purged is : 0
    14-MAY-2013 06:05:23 : finished pruneOpenECIDs
    14-MAY-2013 06:05:23 : total_rows_after_pruning 0
    14-MAY-2013 06:05:23 : calling soa_orabpel.deleteComponentInstances
    14-MAY-2013 06:05:23 Number of rows in table temp_cube_instance Inserted = 0
    14-MAY-2013 06:05:23 Number of rows in table temp_document_ci_ref Inserted = 0
    14-MAY-2013 06:05:23 Number of rows in table temp_document_dlv_msg_ref Inserted = 0
    14-MAY-2013 06:05:23 Number of rows in table HEADERS_PROPERTIES purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table AG_INSTANCE purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table TEST_DETAILS purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table CUBE_SCOPE purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table AUDIT_COUNTER purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table AUDIT_TRAIL purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table AUDIT_DETAILS purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table CI_INDEXES purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table WORK_ITEM purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table WI_FAULT purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table XML_DOCUMENT purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table DOCUMENT_DLV_MSG_REF purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table DOCUMENT_CI_REF purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table DLV_MESSAGE purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table DLV_SUBSCRIPTION purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table CUBE_INSTANCE purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BPM_AUDIT_QUERY purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BPM_MEASUREMENT_ACTIONS purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BPM_MEASUREMENT_ACTION_EXCEPS purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BPM_CUBE_AUDITINSTANCE purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BPM_CUBE_TASKPERFORMANCE purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BPM_CUBE_PROCESSPERFORMANCE purged is : 0
    14-MAY-2013 06:05:23 : completed soa_orabpel.deleteComponentInstances
    14-MAY-2013 06:05:23 : calling workflow.deleteComponentInstances
    14-MAY-2013 06:05:23 : workflow.deleteComponentInstance begin
    14-MAY-2013 06:05:23 Number of rows in table temp_wftask_purge Inserted = 0
    14-MAY-2013 06:05:23 : Purging WFTask_TL
    14-MAY-2013 06:05:23 Number of rows in table WFTask_TL purged is : 0
    14-MAY-2013 06:05:23 : Purging WFTaskHistory
    14-MAY-2013 06:05:23 Number of rows in table WFTaskHistory purged is : 0
    14-MAY-2013 06:05:23 : Purging WFTaskHistory_TL
    14-MAY-2013 06:05:23 Number of rows in table WFTaskHistory_TL purged is : 0
    14-MAY-2013 06:05:23 : Purging WFComments
    14-MAY-2013 06:05:23 Number of rows in table WFComments purged is : 0
    14-MAY-2013 06:05:23 : Purging WFMessageAttribute
    14-MAY-2013 06:05:23 Number of rows in table WFMessageAttribute purged is : 0
    14-MAY-2013 06:05:23 : Purging WFAttachment
    14-MAY-2013 06:05:23 Number of rows in table WFAttachment purged is : 0
    14-MAY-2013 06:05:23 : Purging WFAssignee
    14-MAY-2013 06:05:23 Number of rows in table WFAssignee purged is : 0
    14-MAY-2013 06:05:23 : Purging WFReviewer
    14-MAY-2013 06:05:23 Number of rows in table WFReviewer purged is : 0
    14-MAY-2013 06:05:23 : Purging WFCollectionTarget
    14-MAY-2013 06:05:23 Number of rows in table WFCollectionTarget purged is : 0
    14-MAY-2013 06:05:23 : Purging WFRoutingSlip
    14-MAY-2013 06:05:23 Number of rows in table WFRoutingSlip purged is : 0
    14-MAY-2013 06:05:23 : Purging WFNotification
    14-MAY-2013 06:05:23 Number of rows in table WFNotification purged is : 0
    14-MAY-2013 06:05:23 : Purging WFTaskTimer
    14-MAY-2013 06:05:23 Number of rows in table WFTaskTimer purged is : 0
    14-MAY-2013 06:05:23 : Purging WFTaskError
    14-MAY-2013 06:05:23 Number of rows in table WFTaskError purged is : 0
    14-MAY-2013 06:05:23 : Purging WFHeaderProps
    14-MAY-2013 06:05:23 Number of rows in table WFHeaderProps purged is : 0
    14-MAY-2013 06:05:23 : Purging WFEvidence
    14-MAY-2013 06:05:23 Number of rows in table WFEvidence purged is : 0
    14-MAY-2013 06:05:23 : Purging WFTaskAssignmentStatistic
    14-MAY-2013 06:05:23 Number of rows in table WFTaskAssignmentStatistic purged is : 0
    14-MAY-2013 06:05:23 : Purging WFTaskAggregation
    14-MAY-2013 06:05:23 Number of rows in table WFTaskAggregation purged is : 0
    14-MAY-2013 06:05:23 : Purging WFTask
    14-MAY-2013 06:05:23 Number of rows in table WFTask purged is : 0
    14-MAY-2013 06:05:23 : workflow.deleteComponentInstance end
    14-MAY-2013 06:05:23 : completed workflow.deleteComponentInstances
    14-MAY-2013 06:05:23 : calling mediator.deleteComponentInstances
    14-MAY-2013 06:05:23 Number of rows in table temp_mediator_instance Inserted = 0
    14-MAY-2013 06:05:23 Number of rows in table mediator_payload purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table mediator_deferred_message purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table mediator_document purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table mediator_case_detail purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table mediator_case_instance purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table mediator_instance purged is : 0
    14-MAY-2013 06:05:23 : completed mediator.deleteComponentInstances
    14-MAY-2013 06:05:23 : calling decision.deleteComponentInstances
    14-MAY-2013 06:05:23 Number of rows in table temp_brdecision_instance Inserted = 0
    14-MAY-2013 06:05:23 Number of rows in table BRDecisionFault purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BRDecisionUnitOfWork purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table BRDecisonInstance purged is : 0
    14-MAY-2013 06:05:23 : completed decision.deleteComponentInstances
    14-MAY-2013 06:05:23 : calling fabric.deleteComponentInstances
    14-MAY-2013 06:05:23 Number of rows in table reference_instance_purge inserted = 0
    14-MAY-2013 06:05:23 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table reference_instance purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table rejected_msg_native_payload purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table composite_instance_fault purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table composite_sensor_value purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table composite_instance_assoc purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table component_instance purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table attachment purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table attachment_ref purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table composite_instance purged is : 0
    14-MAY-2013 06:05:23 : completed fabric.deleteComponentInstances
    14-MAY-2013 06:05:23 : time check
    14-MAY-2013 06:05:23 : sysdate = 14/MAY/2013:06/05
    14-MAY-2013 06:05:23 : stoptime = 14/MAY/2013:07/05
    14-MAY-2013 06:05:23 Number of rows in table rejected_message_purge inserted = 0
    14-MAY-2013 06:05:23 Number of rows in table xml_document purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table rejected_msg_native_payload purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table instance_payload purged is : 0
    14-MAY-2013 06:05:23 Number of rows in table rejected_message purged is : 0
    14-MAY-2013 06:05:23 : delete_instances completed successfully
    PL/SQL procedure successfully completed.
    Commit complete.
  • 10. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    Can anyone please help me on this.
  • 11. Re: Unable to purge bpel instances
    KANN Newbie
    Currently Being Moderated
    Hi,
    reduce the batch size (say 100) then it will work.

    Regards,
    KANN

    Edited by: KANN on May 15, 2013 8:05 PM
  • 12. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    Hi Kann,

    Thanks for the response. Still it didn't worked. The script run for around 26 sec but no instances got deleted from composite_instance .

    Thanks
    Vikram
  • 13. Re: Unable to purge bpel instances
    KANN Newbie
    Currently Being Moderated
    Hi,

    The below script worked for me.

    DECLARE
    MAX_CREATION_DATE timestamp;
    MIN_CREATION_DATE timestamp;
    batch_size integer;
    max_runtime integer;
    retention_period timestamp;
    BEGIN
    MIN_CREATION_DATE := to_timestamp('2012-01-01','YYYY-MM-DD');
    MAX_CREATION_DATE := to_timestamp('2013-03-01','YYYY-MM-DD');
    max_runtime := 15;
    batch_size := 200;
    PROD_SOAINFRA.soa.delete_instances(
    min_creation_date => MIN_CREATION_DATE,
    max_creation_date => MAX_CREATION_DATE,
    batch_size => batch_size,
    max_runtime => max_runtime,
    retention_period => retention_period,
    purge_partitioned_component => false);
    exception
    when others then dbms_output.put_line(SUBSTR (SQLERRM, 1, 100));
    END;
    /

    select count(*) from PROD_SOAINFRA.composite_instance where created_time between to_date('01-JAN-12') and to_date('01-MAR-13');

    the above query returned zero rows.

    Regards,
    KANN
  • 14. Re: Unable to purge bpel instances
    1008739 Newbie
    Currently Being Moderated
    Hi Kann,

    Iam currently working on 11.1.1.6 version of bpel with oracle 11g database.Iam not sure why it is not working for me.

    Meanwhile what is the PROD_SOAINFRA ? . Is this application specfiic.

    Iam using this pl/sql

    DECLARE

    MAX_CREATION_DATE timestamp;
    MIN_CREATION_DATE timestamp;
    batch_size integer;
    max_runtime integer;
    retention_period timestamp;

    BEGIN

    MIN_CREATION_DATE := to_timestamp('2011-01-01','YYYY-MM-DD');
    MAX_CREATION_DATE := to_timestamp('2013-05-07','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2013-05-07','YYYY-MM-DD');
    batch_size := 200;
    soa.delete_instances(
    min_creation_date => MIN_CREATION_DATE,
    max_creation_date => MAX_CREATION_DATE,
    batch_size => batch_size,
    max_runtime => max_runtime,
    retention_period => retention_period,
    purge_partitioned_component => false);
    END;

    Thanks
    Vikram

    Edited by: 1005736 on May 16, 2013 3:54 AM

    Edited by: 1005736 on May 16, 2013 3:54 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points