1 2 Previous Next 19 Replies Latest reply: Jun 4, 2013 6:52 AM by 1008739 RSS

    Unable to purge bpel instances

    1008739
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Can anyone please help me on this.
                            • 11. Re: Unable to purge bpel instances
                              KANN
                              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
                                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
                                  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
                                    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