Continued from 'Purging scripts for soa 11g - Part 1' http://yogesh4984.blogspot.in/2012/12/purging-scripts-for-soa-11g-part-1.html
--@@fabric_deleteRejectedMessages.sql
/*
Rem
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/fabric/fabric_deleteRejectedMessages.sql /main/1 2010/05/17 06:09:29 ssudarsa Exp $
Rem
Rem fabric_deleteRejectedMessages.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem fabric_deleteRejectedMessages.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 04/30/10 - created
Rem
*/
procedure deleteRejectedMessages(min_creation_date in timestamp,
max_creation_date in timestamp,
batch_size in integer,
stop_time in Date,
purge_partitioned_component in boolean) is
v_sql varchar2(1000);
v_loop boolean :=true;
total_rows integer;
purge_id_table varchar2(100) :='rejected_message_purge';
begin
while v_loop = true LOOP
v_sql :='insert into ' || purge_id_table || '(id) select id from rejected_message where';
v_sql := v_sql || ' created_time >= ' || '''' || min_creation_date || '''';
v_sql := v_sql || ' and created_time <= ' || '''' || max_creation_date || '''';
v_sql := v_sql || ' and rownum <= ' || batch_size ;
execute immediate 'truncate table ' ||purge_id_table;
execute immediate v_sql;
if SQL%ROWCOUNT < batch_size then
v_loop :=false;
end if;
debug_purge(purge_id_table,'inserted = ');
commit;
delete_instance_payloads(purge_id_table, 'service');
v_sql := 'delete from rejected_message where id in ( select id from ' || purge_id_table ||')';
execute immediate v_sql;
debug_purge('rejected_message');
commit;
if(sysdate >= stop_time) then
v_loop :=false;
end if;
end loop;
exception
when others then
log_error('ERROR (fabric.deleteRejectedMessages)');
raise;
end deleteRejectedMessages;
END soa_fabric;
/
--show errors ;
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--@@decision/purge_decision_oracle.sql
Rem purge_decision_oracle.sql
Rem
Rem Copyright (c) 2009, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_decision_oracle.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Decision Tables
Rem
-- To run bpel alone, create the int_array,ie.
-- create or replace type int_array as varray(10000) of integer;
--First drop temp table before procceding with creation
BEGIN
IF table_exist('TEMP_BRDECISION_INSTANCE') THEN
EXECUTE IMMEDIATE 'DROP TABLE temp_brdecision_instance';
END IF;
EXECUTE IMMEDIATE 'CREATE TABLE temp_brdecision_instance(Id VARCHAR2(100))';
END;
/
CREATE OR REPLACE PACKAGE soa_decision
AS
PROCEDURE deleteComponentInstances ( p_id_table IN VARCHAR2);
PROCEDURE pruneOpenECIDs ( p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP);
FUNCTION deleteNoCompositeIdInstances(p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER,
composite_dn in varchar2) RETURN BOOLEAN;
PROCEDURE deleteComponentInstances(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP,
composite_dn in varchar2);
function isComponentPartitioned return boolean;
END soa_decision;
/
CREATE OR REPLACE
PACKAGE body soa_decision
AS
--@@decision_pruneOpenECIDs.sql
/*
*
*
* pruneOpenCompositeIDs.sql
*
* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* pruneOpenCompositeIDs.sql - BPEL purge prune script
*
*
* DESCRIPTION
* This script is used to prune a list of composite instance ids passed in
* an idTable. Decision uses following criteria to identify a non-purgeable instance :
* - bri state is = 0 (instance is still open)
* - bri.CREATION_TIME is in retention period
*
* Parameters:
* p_older_than TIMESTAMP : Retention period for BPEL instances
* p_id_table : this is the temp table with purge able composite instance ids
* p_prune_table : Add to this table the open instances
*
*/
PROCEDURE pruneOpenECIDs (
p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP)
AS
v_stmt VARCHAR2(2000);
BEGIN
IF p_prune_table is not null then
v_stmt := 'INSERT INTO ' || p_prune_table || ' SELECT comp.ECID from BRDecisionInstance bri, ' ||
p_id_table || ' comp WHERE comp.ECID = bri.ECID AND ( bri.STATE = 0 ' ||
' OR bri.CREATION_TIME >= TO_TIMESTAMP(''RETENTION_PERIOD''))' ;
v_stmt := REPLACE(v_stmt,'RETENTION_PERIOD',p_older_than);
EXECUTE immediate v_stmt;
debug_purge(p_prune_table, 'Inserted ');
END IF;
v_stmt := 'DELETE FROM ' || p_id_table || ' comp WHERE EXISTS ' ||
' (SELECT 1 FROM BRDecisionInstance bri WHERE bri.ECID = comp.ECID ' ||
' AND (bri.STATE = 0 or bri.CREATION_TIME >= TO_TIMESTAMP(''RETENTION_PERIOD'')) )';
v_stmt := REPLACE(v_stmt,'RETENTION_PERIOD',p_older_than);
EXECUTE immediate v_stmt;
debug_purge(p_id_table);
COMMIT;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END pruneOpenECIDs;
--@@decision_deleteComponentInstances.sql
/*
*
* deleteComponentInstances.sql
*
* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteComponentInstances.sql - Decision purge script
*
*
* DESCRIPTION
* This script is used to purge Decision tables provided with a list of ECIDs
* in an idTable.
*
* For a user with multi cpu machine, usage of parallel dml may help. Add this to master script.
* execute immediate 'alter session enable parallel dml';
*
*/
/*
* Procedure: purge_decision_tables
*
* Description
* This procedure purges following Decision tables based on temp tables created in earlier step.
*
* BRDecisionFault
* BRDecisionUnitOfWork
* BRDecisonInstance
*
*/
PROCEDURE purge_decision_tables
AS
v_stmt VARCHAR2(2000);
BEGIN
-- purge of decision data
v_stmt := 'Purge BRDecisionFault' ;
DELETE FROM BRDecisionFault WHERE INSTANCE_ID in (SELECT tbri.id FROM temp_brdecision_instance tbri);
debug_purge('BRDecisionFault');
COMMIT;
v_stmt := 'Purge BRDecisionUnitOfWork' ;
DELETE FROM BRDecisionUnitOfWork WHERE INSTANCE_ID in (SELECT tbri.id FROM temp_brdecision_instance tbri);
debug_purge('BRDecisionUnitOfWork');
COMMIT;
v_stmt := 'Purge BRDecisionInstance';
DELETE FROM BRDecisionInstance ci WHERE ID in (SELECT tbri.id FROM temp_brdecision_instance tbri);
debug_purge('BRDecisonInstance');
COMMIT ;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END purge_decision_tables;
/*
* Procedure : deleteComponentInstances
*
* Description:
* This procedure is provided a table with ECIDs that are purge able.
* Following temp tables are created and passed to purge_decision_tables procedure.
*
* 1. temp_brdecision_instance
* Parameters:
* idTable : Name of the id table that contains list of purge able composite instance IDs
*
*/
PROCEDURE deleteComponentInstances(p_id_table IN VARCHAR2)
AS
v_stmt VARCHAR2(2000);
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_brdecision_instance';
v_stmt := 'INSERT INTO temp_brdecision_instance(ID) ' ||
'SELECT Id FROM BRDecisionInstance bri, '||p_id_table || ' comp WHERE bri.ecid = comp.ECID';
EXECUTE immediate v_stmt;
debug_purge('temp_brdecision_instance', 'Inserted = ');
commit;
purge_decision_tables();
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteComponentInstances;
--@@decision_deleteNoCompositeIdInstances.sql
/*
*
*
* deleteNoCompositeIdInstances.sql
*
* Copyright (c) 2010, 2012, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteNoCompositeIdInstances.sql - Decision purge script used to purge Decision tables
* that do not have a composite instance Id/ECID
*
*
* DESCRIPTION
* This script is used to purge BPEL tables rows that are created with instanace tracking off
*
* Parameters:
* p_older_than Purge all data older than this retention period
* p_rownum Batch size, purge in batch size
*
*
*/
/*
* Procedure: deleteNoCompositeIdInstances
*
* Description
* This procedure purges following Decision tables where ecid is null
*
* BRDecisionFault
* BRDecisionUnitOfWork
* BRDecisonInstance
*
*/
FUNCTION deleteNoCompositeIdInstances( p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER,
composite_dn in varchar2)
RETURN BOOLEAN
AS
v_stmt VARCHAR2(2000);
v_count NUMBER;
v_cmpst_stmt varchar2(2000) := '';
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_brdecision_instance';
v_stmt := 'Create temp_brdecision_instance using CPST_INST_CREATED_TIME' ;
if composite_dn is not null then
v_cmpst_stmt := ' and component_dn like ''' || composite_dn || '%''';
end if;
EXECUTE IMMEDIATE 'INSERT INTO temp_brdecision_instance(ID) ' || 'SELECT ID FROM BRDecisionInstance WHERE STATE != 0 AND CREATION_TIME < '||''''|| p_older_than ||''''|| ' AND CPST_INST_CREATED_TIME BETWEEN '||'''' || p_min_creation_date ||''''|| ' AND ' ||''''|| p_max_creation_date ||''''|| v_cmpst_stmt ||' AND ROWNUM <= ' || p_rownum ||' and ECID is null' ;
debug_purge('temp_brdecision_instance', 'Inserted = ');
if SQL%ROWCOUNT = 0 then
log_info('No Decision instances found with null composite instance ids');
RETURN FALSE;
end if;
commit;
purge_decision_tables();
return TRUE;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteNoCompositeIdInstances ;
--@@decision_deleteComponentInstancesDOP.sql
/*
*
* deleteComponentInstancesDOP.sql
*
* Copyright (c) 2010, 2012, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteComponentInstancesDOP.sql - Decision purge script
*
*
* DESCRIPTION
* This script is used to purge Decision tables provided with a list of composite instance ids
* in an idTable . since teh volume of data for decision is not going to be high , we are not going
* to follow a multi-threaded model for purging decision tables.
*
*/
PROCEDURE deleteComponentInstances(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP,
composite_dn in varchar2)
AS
v_stmt VARCHAR2(2000);
v_rownum INTEGER;
v_cmpst_stmt varchar2(2000) := '';
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_brdecision_instance';
-- Add all ids that have a composite instance id
-- p_idTable has the pruned list, so add all to the temp table
v_stmt := 'INSERT INTO temp_brdecision_instance ' ||
'SELECT bri.id FROM BRDecisionInstance bri, '||p_id_table || ' comp WHERE bri.ecid = comp.ecid';
EXECUTE immediate v_stmt;
debug_purge('temp_brdecision_instance', 'Inserted =');
-- if we still have space, all the no composite instance id rows too
v_rownum := p_max_count - SQL%ROWCOUNT ;
IF v_rownum > 0 THEN
if composite_dn is not null then
v_cmpst_stmt := ' and component_dn like ''' || composite_dn || '%''';
end if;
EXECUTE IMMEDIATE 'INSERT INTO temp_brdecision_instance(ID) ' ||
'SELECT ID FROM BRDecisionInstance WHERE STATE != 0 AND CREATION_TIME < '||''''|| p_older_than ||''''||
'AND CPST_INST_CREATED_TIME BETWEEN '||''''|| p_min_creation_date||''''|| ' AND '||''''||p_max_creation_date ||'''' ||
v_cmpst_stmt ||
'AND ROWNUM <=' || v_rownum||' and ECID is null ';
debug_purge('temp_brdecision_instance ', 'Inserted = ');
END IF ;
purge_decision_tables();
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteComponentInstances;
--@@decision_isComponentPartitioned.sql
function isComponentPartitioned return boolean is
begin
return is_table_partitioned('BRDecisonInstance','CPST_INST_CREATED_TIME');
end isComponentPartitioned;
END soa_decision;
/
--show errors;
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--@@workflow/purge_hwf_oracle.sql
Rem
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/workflow/purge_hwf_oracle.sql st_pcbpel_ssudarsa_blr_backport_11832797_11.1.1.5.0/1 2012/05/18 07:46:18 ssudarsa Exp $
Rem
Rem purge_workflow_oracle.sql
Rem
Rem Copyright (c) 2010, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_workflow_oracle.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Purges workflow related tables
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem vsvetov 05/04/10 - creation
Rem
--First drop temp table before procceding with creation
BEGIN
IF table_exist('TEMP_WFTASK_PURGE') THEN
EXECUTE IMMEDIATE 'DROP TABLE temp_wftask_purge';
END IF;
-- Create temporary tables
EXECUTE IMMEDIATE 'CREATE TABLE TEMP_WFTASK_PURGE(taskId VARCHAR2(64), ecId VARCHAR2(200))';
END;
/
--show errors;
CREATE OR REPLACE PACKAGE soa_workflow
AS
PROCEDURE deleteComponentInstances ( p_id_table IN VARCHAR2);
PROCEDURE pruneOpenECIDs ( p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP);
FUNCTION deleteNoCompositeIdInstances(p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER,
composite_dn in varchar2) RETURN BOOLEAN;
PROCEDURE createTempTables(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP,
composite_dn in varchar2);
FUNCTION deleteComponentInstances(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,
write_file utl_file.file_type default null) RETURN BOOLEAN;
FUNCTION isComponentPartitioned RETURN BOOLEAN;
END soa_workflow;
/
CREATE OR REPLACE PACKAGE BODY soa_workflow
AS
--@@hwf_pruneOpenECIDs.sql
/*
*
*
* pruneOpenCompositeIDs.sql
*
* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* pruneOpenCompositeIDs.sql - BPEL purge prune script
*
*
* DESCRIPTION
* This script is used to prune a list of composite instance ids passed in
* an idTable. BPEL uses following criteria to identify a non-purgeable instance :
* - wf state is not in 'DELETED','ERRORED','EXPIRED','STALE','WITHDRAWN'
* Parameters:
* p_older_than TIMESTAMP : Retention period for BPEL instances
* p_id_table : this is the temp table with purge able composite instance ids
* p_prune_table : Add to this table the open instances
*
*/
PROCEDURE pruneOpenECIDs (
p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP)
AS
v_stmt VARCHAR2(2000);
BEGIN
IF p_prune_table IS NOT NULL THEN
v_stmt := ' INSERT INTO ' || p_prune_table ||
' SELECT comp.ecid ' ||
' FROM WFTask task, ' || p_id_table || ' comp ' ||
' WHERE task.ecid = comp.ecid ' ||
' AND task.state IS NOT NULL ' ||
' AND task.state NOT IN (''DELETED'',''ERRORED'',''EXPIRED'',''STALE'',''WITHDRAWN'')';
execute immediate v_stmt;
COMMIT;
debug_purge(p_prune_table, 'Workflow inserts into prune table = ');
END IF;
v_stmt := ' DELETE FROM ' || p_id_table || ' comp ' ||
' WHERE EXISTS ' ||
' (SELECT 1 FROM WFTask task ' ||
' WHERE task.ecid = comp.ecid ' ||
' AND task.state IS NOT NULL ' ||
' AND task.state NOT IN (''DELETED'',''ERRORED'',''EXPIRED'',''STALE'',''WITHDRAWN''))';
execute immediate v_stmt;
COMMIT;
debug_purge(p_id_table, 'Workflow deletes ECIDs for open WFTask = ');
EXCEPTION
when others then
log_error(v_stmt);
raise;
END pruneOpenECIDs;
--@@hwf_deleteComponentInstances.sql
/*
Rem
Rem wf_deleteCompositeInstances.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem wf_deleteCompositeInstances.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Workflow purge
Rem
Rem MODIFIED (MM/DD/YY)
Rem vumapath 08/06/10 - XbranchMerge vumapath_bug-9872950_ps3_main from
Rem main
Rem wstallar 07/16/10 - Addressing performance concerns
Rem hdoddi 07/16/10 - XbranchMerge hdoddi_bug-9846252_ps3 from main
Rem vumapath 06/25/10 - Purge task attachments
Rem hdoddi 07/02/10 - Adding commit statments bug 9846252
Rem vsvetov 05/04/10 - add support when ecid is null
Rem vsvetov 04/28/10 - creation
Rem
*/
PROCEDURE truncate_temp_tables
AS
BEGIN
--before starting clean up temp tables
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_wftask_purge';
END truncate_temp_tables;
PROCEDURE delete_workflow_instances
AS
v_stmt VARCHAR2(2000);
BEGIN
-- purge of workflow data
-- first, purge each child-table in turn, as there are performance concerns
-- with relying on FK cascade deletes...
log_info('Purging WFTask_TL');
v_stmt := 'Purge WFTask_TL' ;
DELETE FROM WFTask_TL WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTask_TL');
COMMIT;
log_info('Purging WFTaskHistory');
v_stmt := 'Purge WFTaskHistory' ;
DELETE FROM WFTaskHistory WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTaskHistory');
COMMIT;
log_info('Purging WFTaskHistory_TL');
v_stmt := 'Purge WFTaskHistory_TL' ;
DELETE FROM WFTaskHistory_TL WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTaskHistory_TL');
COMMIT;
log_info('Purging WFComments');
v_stmt := 'Purge WFComments' ;
DELETE FROM WFComments WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFComments');
COMMIT;
log_info('Purging WFMessageAttribute');
v_stmt := 'Purge WFMessageAttribute' ;
DELETE FROM WFMessageAttribute WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFMessageAttribute');
COMMIT;
log_info('Purging WFAttachment');
v_stmt := 'Purge WFAttachment' ;
DELETE FROM WFAttachment WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFAttachment');
COMMIT;
log_info('Purging WFAssignee');
v_stmt := 'Purge WFAssignee' ;
DELETE FROM WFAssignee WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFAssignee');
COMMIT;
log_info('Purging WFReviewer');
v_stmt := 'Purge WFReviewer' ;
DELETE FROM WFReviewer WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFReviewer');
COMMIT;
log_info('Purging WFCollectionTarget');
v_stmt := 'Purge WFCollectionTarget' ;
DELETE FROM WFCollectionTarget WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFCollectionTarget');
COMMIT;
log_info('Purging WFRoutingSlip');
v_stmt := 'Purge WFRoutingSlip' ;
DELETE FROM WFRoutingSlip WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFRoutingSlip');
COMMIT;
log_info('Purging WFNotification');
v_stmt := 'Purge WFNotification' ;
DELETE FROM WFNotification WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFNotification');
COMMIT;
log_info('Purging WFTaskTimer');
v_stmt := 'Purge WFTaskTimer' ;
DELETE FROM WFTaskTimer WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTaskTimer');
COMMIT;
log_info('Purging WFTaskError');
v_stmt := 'Purge WFTaskError' ;
DELETE FROM WFTaskError WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTaskError');
COMMIT;
log_info('Purging WFHeaderProps');
v_stmt := 'Purge WFHeaderProps' ;
DELETE FROM WFHeaderProps WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFHeaderProps');
COMMIT;
log_info('Purging WFEvidence');
v_stmt := 'Purge WFEvidence' ;
DELETE FROM WFEvidence WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFEvidence');
COMMIT;
log_info('Purging WFTaskAssignmentStatistic');
v_stmt := 'Purge WFTaskAssignmentStatistic' ;
DELETE FROM WFTaskAssignmentStatistic WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTaskAssignmentStatistic');
COMMIT;
log_info('Purging WFTaskAggregation');
v_stmt := 'Purge WFTaskAggregation' ;
DELETE FROM WFTaskAggregation WHERE rootTaskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTaskAggregation');
COMMIT;
-- Now purge main task table
log_info('Purging WFTask');
v_stmt := 'Purge WFTask' ;
DELETE FROM WFTask WHERE taskId in (SELECT twfp.taskId FROM temp_wftask_purge twfp);
debug_purge('WFTask');
COMMIT;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END delete_workflow_instances;
PROCEDURE deleteComponentInstances(p_id_table IN VARCHAR2)
AS
v_stmt VARCHAR2(2000);
v_count NUMBER;
BEGIN
log_info('workflow.deleteComponentInstance begin');
truncate_temp_tables();
v_stmt := 'INSERT INTO temp_wftask_purge (taskId, ecId) ' ||
'SELECT task.taskId, task.ecId ' ||
' FROM WFTASK task, '||p_id_table || ' comp ' ||
' WHERE task.ecid = comp.ecId';
EXECUTE IMMEDIATE v_stmt;
COMMIT;
debug_purge('temp_wftask_purge', 'Inserted = ');
delete_workflow_instances();
log_info('workflow.deleteComponentInstance end');
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteComponentInstances;
--@@hwf_deleteNoCompositeIdInstances.sql
/*
*
*
* deleteNoCompositeIdInstances.sql
*
* Copyright (c) 2010, 2012, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteNoCompositeIdInstances.sql - BPEL purge script used to purge BPEL tables
* that do not have a composite instance Id
*
*
* DESCRIPTION
* This script is used to purge BPEL tables rows that are created with instanace tracking off
*
* Parameters:
* p_older_than Purge all data older than this retention period
* p_rownum Batch size, purge in batch size
*
*
*/
/*
* Procedure: deleteNoCompositeIdInstances
*
* Description
* This procedure purges following BPEL tables where ecid is null
*
* WFTASK
*
*/
FUNCTION deleteNoCompositeIdInstances( p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP,
p_rownum NUMBER,
composite_dn in varchar2)
RETURN BOOLEAN
AS
v_stmt VARCHAR2(2000);
v_cmpst_stmt varchar2(2000) := '';
BEGIN
log_info('workflow.deleteNoCompositeIdInstances begin');
truncate_temp_tables();
v_stmt := 'Populate temp_wftaks_purge using createdDate' ;
log_info('workflow.deleteNoCompositeIdInstances populates temp_wftaks_purge using createdDate between min_date=' || p_min_creation_date || 'and max_date=' || p_max_creation_date);
if composite_dn is not null then
v_cmpst_stmt := ' and task.compositedn like ''' || composite_dn || '%''';
end if;
v_stmt :=
' INSERT INTO temp_wftask_purge(taskId,ecId) ' ||
' SELECT task.taskId, task.ecId ' ||
' FROM WFTask task ' ||
' WHERE task.ecid is NULL ' ||
' AND ( task.state is null OR ' ||
' task.state in (''DELETED'',''ERRORED'',''EXPIRED'',''STALE'',''WITHDRAWN''))' ||
' AND task.createdDate BETWEEN ' || '''' || CAST(p_min_creation_date AS DATE) || '''' ||
' AND ' || '''' || CAST(p_max_creation_date AS DATE) || '''' ||
v_cmpst_stmt ||
' AND rownum <= '|| p_rownum ||
' AND NOT EXISTS ( ' ||
' SELECT task2.parenttaskid ' ||
' FROM wftask task2 ' ||
' WHERE task.taskId=task2.parenttaskid ' ||
' AND task2.state in (''ASSIGNED'', ''ALERTED'', ''INFO_REQUESTED'', ''OUTCOME_UPDATED'', ''SUSPENDED'') ) ';
if SQL%ROWCOUNT = 0 then
log_info('workflow.deleteNoCompositeIdInstances done. No WFTask instances were found with ecId equals null');
RETURN FALSE;
end if;
debug_purge('temp_wftask_purge', 'Inserted = ');
COMMIT;
delete_workflow_instances();
log_info('workflow.deleteNoCompositeIdInstances continue ...');
return TRUE;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteNoCompositeIdInstances ;
--@@hwf_deleteComponentInstancesDOP.sql
/*
Rem
Rem wf_deleteCompositeInstancesDOP.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem wf_deleteCompositeInstancesDOP.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Workflow purge
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem vsvetov 10/19/10 - performance improvements bug 10206390
Rem wstallar 07/28/10 - 9937600: addressing performance concerns
Rem vsvetov 05/04/10 - add use case when eicid is null
Rem vsvetov 04/28/10 - creation
Rem
*/
/*
* Procedure: deleteComponentInstaces
*
* Description
* This procedure purges following BPM tables based on temp tables created in an earlier step.
*
* WFTASK
*
* Note: You need to obtain EXECUTE privilege to run this procedure.
* GRANT EXECUTE ON dbms_lock TO user_name
*
*/
FUNCTION deleteComponentInstances(p_dop NUMBER,
p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,
write_file utl_file.file_type default NULL)
RETURN BOOLEAN
AS
v_stmt VARCHAR2(2000);
v_deleted boolean:= TRUE;
v_counter NUMBER := 1;
CURSOR c_temp_wftask_purge_thread IS
SELECT twfp.taskId
FROM temp_wftask_purge twfp
WHERE ( ( twfp.ecid IS NULL AND
mod (dbms_utility.get_hash_value(twfp.taskId,0,p_dop), p_dop)=p_thread
) OR
( twfp.ecid IS NOT NULL AND
mod (dbms_utility.get_hash_value(twfp.ecId,0,p_dop), p_dop)=p_thread
)
);
TYPE purge_taskid_type IS TABLE OF temp_wftask_purge.taskId%TYPE;
l_task_instances purge_taskid_type;
BEGIN
v_deleted := true;
log_info('Begin WFTASK purge for thread = ' || p_thread,write_file);
OPEN c_temp_wftask_purge_thread;
WHILE v_deleted LOOP
v_deleted := false;
-- Fetch next p_batch_size records from the temp table
FETCH c_temp_wftask_purge_thread BULK COLLECT INTO l_task_instances LIMIT p_batch_size;
log_info('Purging '|| l_task_instances.COUNT||' task instances on loop = '||v_counter,write_file);
--If the number of instances was same as the the batch size, then there are more
--rows to fetch and delete after this batch...
IF ( l_task_instances.COUNT = p_batch_size ) THEN
v_deleted := true;
END IF;
-- Delete from each of the WFTask tables in turn, in a separate transaction
-- This is more performant than relying on cascade deletes from the WFTask table
v_stmt := 'Purge WFTask_TL';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFTask_TL WFTask_TL(taskId)) */ FROM WFTask_TL WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTask_TL', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFTaskHistory';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFTaskHistory WFTaskHistory(taskId)) */ FROM WFTaskHistory WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTaskHistory', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFTaskHistory_TL';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFTaskHistory_TL WFTaskHistory_TL(taskId)) */ FROM WFTaskHistory_TL WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTaskHistory_TL', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFComments';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFComments WFComments(taskId)) */ FROM WFComments WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFComments', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFMessageAttribute';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFMessageAttribute WFMessageAttribute(taskId)) */ FROM WFMessageAttribute WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFMessageAttribute', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFAttachment';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFAttachment WFAttachment(taskId)) */ FROM WFAttachment WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFAttachment', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFAssignee';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFAssignee WFAssignee(taskId)) */ FROM WFAssignee WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFAssignee', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFReviewer';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFReviewer WFReviewer(taskId)) */ FROM WFReviewer WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFReviewer', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFCollectionTarget';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ WFCollectionTarget WFCollectionTarget(taskId)) */ FROM WFCollectionTarget WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFCollectionTarget', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFRoutingSlip';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFRoutingSlip WFRoutingSlip(taskId)) */ FROM WFRoutingSlip WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFRoutingSlip', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFNotification';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFNotification WFNotification(taskId)) */ FROM WFNotification WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFNotification', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFTaskTimer';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFTaskTimer WFTaskTimer(taskId)) */ FROM WFTaskTimer WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTaskTimer', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFTaskError';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFTaskError WFTaskError(taskId)) */ FROM WFTaskError WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTaskError', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFHeaderProps';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFHeaderProps WFHeaderProps(taskId)) */ FROM WFHeaderProps WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFHeaderProps', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFEvidence';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFEvidence WFEvidence(taskId)) */ FROM WFEvidence WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFEvidence', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFTaskAssignmentStatistic';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFTaskAssignmentStatistic WFTaskAssignmentStatistic(taskId)) +*/ FROM WFTaskAssignmentStatistic WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTaskAssignmentStatistic', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WFTaskAggregation';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFTaskAggregation WFTaskAggregation(taskId)) */ FROM WFTaskAggregation WHERE rootTaskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTaskAggregation', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
debug_purge('WFTaskAggregation', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
--Purge from the WFTask table last, so there will be no cascade deletes (all child table
--rows will already be deleted
v_stmt := 'Purge WFTASK ';
FORALL i in l_task_instances.FIRST..l_task_instances.LAST
DELETE /*+ INDEX(WFWFTask WFTask(taskId)) */ FROM WFTask WHERE taskId = l_task_instances(i);
IF SQL%FOUND THEN
debug_purge('WFTASK', 'purged workflow for thread = ' || p_thread || 'loop = '|| v_counter,write_file);
COMMIT;
END IF;
log_info('workflow purge loop counter =' || v_counter || ' for thead =' || p_thread, write_file);
log_info('workflow time check: sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'), write_file);
log_info('workflow p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'), write_file);
v_counter := v_counter + 1;
-- exit loop if out of ime
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE;
CLOSE c_temp_wftask_purge_thread;
return FALSE ;
END IF;
END LOOP;
CLOSE c_temp_wftask_purge_thread;
log_info('end of workflow.deleteComponentInstancesDOP ',write_file );
return TRUE;
EXCEPTION
when others then
log_error(v_stmt,write_file);
raise;
END deleteComponentInstances;
--@@hwf_isComponentPartitioned.sql
function isComponentPartitioned return boolean is
begin
return is_table_partitioned('WFTASK','COMPOSITECREATEDTIME');
end isComponentPartitioned;
--@@hwf_createTempTables.sql
/*
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/workflow/hwf_createTempTables.sql st_pcbpel_ssudarsa_blr_backport_11832797_11.1.1.5.0/1 2012/05/18 07:46:17 ssudarsa Exp $
Rem
Rem hwf_createTempTables.sql
Rem
Rem Copyright (c) 2010, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem hwf_createTempTables.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Workflow purge
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem hdoddi 07/16/10 - XbranchMerge hdoddi_bug-9846252_ps3 from main
Rem hdoddi 07/02/10 - Adding commit stmts bug 9846252
Rem vsvetov 05/04/10 - creation
Rem
*/
PROCEDURE createTempTables(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP,
composite_dn in varchar2)
AS
v_stmt VARCHAR2(2000);
v_rownum INTEGER;
v_cmpst_stmt varchar2(2000) := '';
BEGIN
truncate_temp_tables() ;
v_stmt := 'INSERT INTO temp_wftask_purge(taskId,ecId) ' ||
'SELECT task.taskId,task.ecId ' ||
' FROM WFTask task, '||p_id_table || ' comp ' ||
' WHERE task.ecid = comp.ecid';
EXECUTE immediate v_stmt;
COMMIT;
debug_purge('temp_wftask_purge', 'Inserted =');
-- If we still have space then add "NoCompositeInstanceIds" also
v_rownum := p_max_count - SQL%ROWCOUNT;
IF v_rownum > 0 THEN
if composite_dn is not null then
v_cmpst_stmt := ' and task.compositedn like ''' || composite_dn || '%''';
end if;
EXECUTE IMMEDIATE 'INSERT INTO temp_wftask_purge(taskId,ecId) ' ||
'SELECT task.taskId,task.ecId ' ||
' FROM WFTask task ' ||
' WHERE task.ecid is NULL ' ||
' AND ( task.state is null OR ' ||
' task.state in (''DELETED'',''ERRORED'',''EXPIRED'',''STALE'',''WITHDRAWN''))' ||
' AND task.createdDate BETWEEN ' || '''' || CAST(p_min_creation_date as DATE) || '''' ||
' AND ' || '''' || CAST(p_max_creation_date as DATE) || '''' ||
v_cmpst_stmt ||
' AND ROWNUM <= ' || v_rownum ||
' AND NOT EXISTS ( ' ||
' SELECT task2.parenttaskid ' ||
' FROM wftask task2 ' ||
' WHERE task.taskId=task2.parenttaskid ' ||
' AND task2.state in (''ASSIGNED'', ''ALERTED'', ''INFO_REQUESTED'', ''OUTCOME_UPDATED'', ''SUSPENDED'') ) ';
COMMIT;
debug_purge('temp_wftask_purge', 'Inserted =');
END IF;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END createTempTables;
END soa_workflow;
/
--show errors;
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--@@mediator/purge_mediator_oracle.sql
Rem
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/mediator/purge_mediator_oracle.sql st_pcbpel_ssudarsa_blr_backport_11832797_11.1.1.5.0/1 2012/05/18 07:46:17 ssudarsa Exp $
Rem
Rem purge_mediator_oracle.sql
Rem
Rem Copyright (c) 2010, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_mediator_oracle.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Mediator tables
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem sanjain 04/25/10 - Created
Rem
--Drop temp table for mediator instances
BEGIN
IF table_exist('TEMP_MEDIATOR_INSTANCE') THEN
EXECUTE IMMEDIATE 'DROP TABLE temp_mediator_instance';
END IF;
-- Create temporary table
EXECUTE IMMEDIATE 'CREATE TABLE temp_mediator_instance
(
id varchar2(100),
ecid varchar2(100)
)';
END;
/
CREATE OR REPLACE PACKAGE soa_mediator
AS
PROCEDURE deleteComponentInstances ( p_id_table IN VARCHAR2);
PROCEDURE pruneOpenECIDs ( p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP);
FUNCTION deleteNoCompositeIdInstances(p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER,
composite_dn in varchar2) RETURN BOOLEAN;
PROCEDURE createTempTables(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP,
composite_dn in varchar2);
FUNCTION deleteComponentInstances(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file utl_file.file_type default null) RETURN BOOLEAN;
FUNCTION isComponentPartitioned RETURN BOOLEAN;
END soa_mediator;
/
--show errors;
CREATE OR REPLACE PACKAGE body soa_mediator
AS
--@@mediator_pruneOpenECIDs.sql
/*
Rem $Header: pcbpel/admin/sqlutils/purge_enhanced/oracle/mediator/mediator_pruneOpenECIDs.sql /main/1 2010/04/30 10:23:37 sanjain Exp $
Rem
Rem mediator_pruneOpenECIDs.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem mediator_pruneOpenECIDs.sql - Mediator prune open ECID sql
Rem
Rem DESCRIPTION
Rem This scripts prunes the list of ECIDs which are not purgable w.r.t
Rem mediator using the following criteria:
Rem mediator_instance.component_state between 4 and 15(when the instance is in recovery required state or running)
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem sanjain 04/27/10 - Created
Rem
*/
PROCEDURE pruneOpenECIDs (
p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP)
AS
v_stmt VARCHAR2(2000);
BEGIN
IF p_prune_table is not null then
v_stmt := 'INSERT INTO ' || p_prune_table || ' SELECT comp.ECID from MEDIATOR_INSTANCE mi, ' ||
p_id_table || ' comp WHERE comp.ECID = mi.ECID AND mi.COMPONENT_STATE between 4 and 15';
EXECUTE immediate v_stmt;
debug_purge(p_prune_table, 'Inserted ');
END IF;
v_stmt := 'DELETE FROM ' || p_id_table || ' comp WHERE EXISTS ' ||
' (SELECT 1 FROM MEDIATOR_INSTANCE mi WHERE mi.ECID = comp.ECID AND mi.COMPONENT_STATE between 4 and 15)';
EXECUTE immediate v_stmt;
debug_purge(p_id_table);
COMMIT;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END pruneOpenECIDs;
--@@mediator_deleteComponentInstances.sql
/*
Rem
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/mediator/mediator_deleteComponentInstances.sql /st_pcbpel_11.1.1.4.0/1 2010/09/01 04:21:49 apjain Exp $
Rem
Rem mediator_deleteComponentInstances.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem mediator_deleteComponentInstances.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Mediator purge
Rem
Rem MODIFIED (MM/DD/YY)
Rem sanjain 04/27/10 - Created
Rem
*/
PROCEDURE truncate_temp_tables
AS
BEGIN
--before starting clean up temp tables
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_mediator_instance';
END truncate_temp_tables;
-- Function to distinguish between 11gR1PS1 and 11gR1PS2 schema
function is_ps1_schema return boolean is
col_found integer := 0;
begin
select count(1) into col_found from user_tab_columns where table_name='MEDIATOR_AUDIT_DOCUMENT' and column_name='INSTANCE_ID';
if col_found = 1 then
return true;
else
return false;
end if;
end is_ps1_schema;
PROCEDURE delete_mediator_instances
AS
BEGIN
-- purge mediator_payload
DELETE from mediator_payload where owner_id in(
select dummy1 from mediator_deferred_message a where exists(select id from temp_mediator_instance b
where b.id=a.msg_id)
union select id from mediator_case_instance c where exists(select id from temp_mediator_instance d
where d.id=c.instance_id));
COMMIT;
debug_purge('mediator_payload');
-- purge mediator_deferred_message
DELETE from mediator_deferred_message a where exists(select id from temp_mediator_instance b where b.id=a.msg_id);
COMMIT;
debug_purge('mediator_deferred_message');
-- purge mediator_document
if is_ps1_schema() = true then
DELETE from mediator_audit_document a where exists(select id from temp_mediator_instance b where b.id=a.instance_id);
COMMIT;
debug_purge('mediator_document');
end if;
-- purge mediator_case_detail
DELETE from mediator_case_detail a where exists(select id from temp_mediator_instance b where b.id=a.instance_id);
COMMIT;
debug_purge('mediator_case_detail');
-- purge mediator_case_instance
DELETE from mediator_case_instance a where exists(select id from temp_mediator_instance b where b.id=a.instance_id);
COMMIT;
debug_purge('mediator_case_instance');
-- purge mediator_instance
DELETE from mediator_instance a where exists(select id from temp_mediator_instance b where b.id=a.id);
COMMIT;
debug_purge('mediator_instance');
END delete_mediator_instances;
-- To purge mediator instances for the ecids present in p_id_table
PROCEDURE deleteComponentInstances(p_id_table IN VARCHAR2)
AS
v_stmt VARCHAR2(2000);
BEGIN
truncate_temp_tables();
v_stmt := 'INSERT into temp_mediator_instance(id) SELECT id from mediator_instance a
where exists(select id from ' || p_id_table || ' b where b.ecid=a.ecid)';
EXECUTE immediate v_stmt;
debug_purge('temp_mediator_instance', 'Inserted = ');
delete_mediator_instances();
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteComponentInstances;
--@@mediator_deleteNoCompositeIdInstances.sql
/*
Rem
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/mediator/mediator_deleteNoCompositeIdInstances.sql st_pcbpel_ssudarsa_blr_backport_11832797_11.1.1.5.0/1 2012/05/18 07:46:17 ssudarsa Exp $
Rem
Rem mediator_deleteNoCompositeIdInstances.sql
Rem
Rem Copyright (c) 2010, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem mediator_deleteNoCompositeIdInstances.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem sanjain 04/27/10 - Created
Rem
*/
FUNCTION deleteNoCompositeIdInstances( p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER,
composite_dn in varchar2)
RETURN BOOLEAN
AS
v_stmt VARCHAR2(2000);
bool1 BOOLEAN;
bool2 BOOLEAN;
bool3 BOOLEAN;
v_cmpst_stmt varchar2(2000) := '';
BEGIN
truncate_temp_tables();
v_stmt := 'Populate temp_mediator_instance using composite_creation_date' ;
if composite_dn is not null then
v_cmpst_stmt := ' and component_name like ''' || composite_dn || '%''';
end if;
EXECUTE IMMEDIATE 'INSERT INTO temp_mediator_instance(id) ' || 'SELECT id FROM MEDIATOR_INSTANCE WHERE (COMPONENT_STATE <= 3 OR COMPONENT_STATE = 16) AND COMPOSITE_CREATION_DATE BETWEEN '||'''' || p_min_creation_date ||''''|| ' AND ' ||''''|| p_max_creation_date ||''''|| v_cmpst_stmt || ' AND ROWNUM <= ' || p_rownum ||' and (COMPOSITE_INSTANCE_ID is null OR COMPOSITE_INSTANCE_ID = 0)';
debug_purge('temp_mediator_instance', 'Inserted = ');
if SQL%ROWCOUNT = 0 then
log_info('No Mediator instances found with composite instance id as null or zero');
RETURN FALSE;
end if;
delete_mediator_instances();
return TRUE;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteNoCompositeIdInstances ;
--@@mediator_deleteComponentInstancesDOP.sql
/*
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/mediator/mediator_deleteComponentInstancesDOP.sql /main/2 2010/05/11 21:32:45 ykumar Exp $
Rem
Rem mediator_deleteComponentInstancesDOP.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem mediator_deleteComponentInstancesDOP.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem sanjain 04/27/10 - Created
Rem
*/
FUNCTION deleteComponentInstances(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file utl_file.file_type default NULL)
RETURN BOOLEAN
AS
v_stmt VARCHAR2(2000);
v_deleted boolean:=true;
v_counter NUMBER := 1;
f1_flag boolean:=true;
f2_flag boolean:=true;
f3_flag boolean:=true;
f4_flag boolean:=true;
f5_flag boolean:=true;
f6_flag boolean:=true;
v_stoptime date :=p_stop_time;
BEGIN
v_deleted := true;
while v_deleted LOOP
v_deleted := false;
log_info('Begin Mediator purge loop ' || v_counter || ' for thread = ' || p_thread,write_file);
v_stmt := 'Purge MEDIATOR_PAYLOAD' ;
IF f1_flag then
f1_flag:=false;
DELETE from mediator_payload where owner_id in(
select case_id from mediator_deferred_message a where exists(select id from temp_mediator_instance b where b.id=a.msg_id
AND (
(b.ecid IS NULL AND mod (dbms_utility.get_hash_value(b.id,0,p_dop), p_dop)=p_thread)
OR (b.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(b.ecid,0,p_dop), p_dop)=p_thread)))
AND rownum < p_batch_size);
IF SQL%FOUND THEN
v_deleted := true;
f1_flag:=true;
END IF;
END IF;
debug_purge('MEDIATOR_PAYLOAD');
COMMIT;
v_stmt := 'Purge MEDIATOR_DEFERRED_MESSAGE' ;
IF f2_flag then
f2_flag:=false;
DELETE from mediator_deferred_message a where exists(select id from temp_mediator_instance b where b.id=a.msg_id
AND ((b.ecid IS NULL AND mod (dbms_utility.get_hash_value(b.id,0,p_dop), p_dop)=p_thread)
OR (b.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(b.ecid,0,p_dop), p_dop)=p_thread)))
AND rownum < p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f2_flag:=true;
END IF;
END IF;
debug_purge('MEDIATOR_DEFERRED_MESSAGE');
COMMIT;
IF is_ps1_schema() = true then
v_stmt := 'Purge MEDIATOR_DOCUMENT' ;
IF f3_flag then
f3_flag:=false;
DELETE from mediator_audit_document a where exists(select id from temp_mediator_instance b where b.id=a.instance_id
AND ((b.ecid IS NULL AND mod (dbms_utility.get_hash_value(b.id,0,p_dop), p_dop)=p_thread)
OR (b.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(b.ecid,0,p_dop), p_dop)=p_thread)))
AND rownum < p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f3_flag:=true;
END IF;
END IF;
debug_purge('MEDIATOR_DOCUMENT');
COMMIT;
END IF;
v_stmt := 'Purge MEDIATOR_CASE_DETAIL' ;
IF f4_flag then
f4_flag:=false;
DELETE from mediator_case_detail a where exists(select id from temp_mediator_instance b where b.id=a.instance_id
AND ((b.ecid IS NULL AND mod (dbms_utility.get_hash_value(b.id,0,p_dop), p_dop)=p_thread)
OR (b.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(b.ecid,0,p_dop), p_dop)=p_thread)))
AND rownum < p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f4_flag:=true;
END IF;
END IF;
debug_purge('MEDIATOR_CASE_DETAIL');
COMMIT;
v_stmt := 'Purge MEDIATOR_CASE_INSTANCE' ;
IF f5_flag then
f5_flag:=false;
DELETE from mediator_case_instance a where exists(select id from temp_mediator_instance b where b.id=a.instance_id
AND ((b.ecid IS NULL AND mod (dbms_utility.get_hash_value(b.id,0,p_dop), p_dop)=p_thread)
OR (b.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(b.ecid,0,p_dop), p_dop)=p_thread)))
AND rownum < p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f5_flag:=true;
END IF;
END IF;
debug_purge('MEDIATOR_CASE_INSTANCE');
COMMIT;
v_stmt := 'Purge MEDIATOR_INSTANCE' ;
IF f6_flag then
f6_flag:=false;
DELETE from mediator_instance a where exists(select id from temp_mediator_instance b where b.id=a.id
AND ((b.ecid IS NULL AND mod (dbms_utility.get_hash_value(b.id,0,p_dop), p_dop)=p_thread)
OR (b.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(b.ecid,0,p_dop), p_dop)=p_thread)))
AND rownum < p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f6_flag:=true;
END IF;
END IF;
debug_purge('MEDIATOR_INSTANCE');
COMMIT;
v_counter := v_counter + 1;
-- exit loop if out of time
IF (sysdate >= v_stoptime) THEN
v_deleted := FALSE;
return FALSE ;
END IF;
END LOOP;
return TRUE;
EXCEPTION
when others then
log_error(v_stmt,write_file);
raise;
END deleteComponentInstances;
--@@mediator_isComponentPartitioned.sql
/*
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/mediator/mediator_isComponentPartitioned.sql /main/2 2010/05/11 21:32:45 ykumar Exp $
Rem
Rem mediator_isComponentPartitioned.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem mediator_isComponentPartitioned.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Mediator purge
Rem
Rem MODIFIED (MM/DD/YY)
Rem sanjain 04/27/10 - Created
Rem
*/
function isComponentPartitioned return boolean is
begin
return is_table_partitioned('MEDIATOR_INSTANCE','COMPOSITE_CREATION_DATE');
end isComponentPartitioned;
--@@mediator_createTempTables.sql
/*
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/mediator/mediator_createTempTables.sql st_pcbpel_ssudarsa_blr_backport_11832797_11.1.1.5.0/1 2012/05/18 07:46:17 ssudarsa Exp $
Rem
Rem mediator_createTempTables.sql
Rem
Rem Copyright (c) 2010, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem mediator_createTempTables.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Mediator purge
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem sanjain 07/13/10 - Fixing bug 9779594
Rem sanjain 04/27/10 - Created
Rem
*/
PROCEDURE createTempTables(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP,
composite_dn in varchar2)
AS
v_stmt VARCHAR2(2000);
v_rownum INTEGER;
v_cmpst_stmt varchar2(2000) := '';
BEGIN
truncate_temp_tables() ;
-- p_idTable has the pruned list, so add all to the temp table
v_stmt := 'INSERT INTO temp_mediator_instance(id,ecid) ' ||
'SELECT mi.id,mi.ecid FROM mediator_instance mi, '||p_id_table || '
comp WHERE mi.ecid = comp.ecid';
EXECUTE immediate v_stmt;
debug_purge('temp_mediator_instance', 'Inserted =');
commit;
-- If we still have space then add "NoCompositeInstanceIds" also
v_rownum := p_max_count - SQL%ROWCOUNT;
IF v_rownum > 0 THEN
if composite_dn is not null then
v_cmpst_stmt := ' and component_name like ''' || composite_dn || '%''';
end if;
EXECUTE IMMEDIATE 'INSERT INTO temp_mediator_instance(id,ecid) ' || 'SELECT id,ecid FROM MEDIATOR_INSTANCE WHERE (COMPONENT_STATE <= 3 OR COMPONENT_STATE = 16) AND COMPOSITE_CREATION_DATE BETWEEN '||'''' || p_min_creation_date ||''''|| ' AND ' ||''''|| p_max_creation_date ||''''||
v_cmpst_stmt ||
' AND ROWNUM <= ' || v_rownum ||' AND (COMPOSITE_INSTANCE_ID is null OR COMPOSITE_INSTANCE_ID = 0)';
debug_purge('temp_mediator_instance', 'Inserted =');
commit;
END IF;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END createTempTables;
END soa_mediator;
/
--show errors;
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--@@b2b/purge_b2b_oracle.sql
Rem
Rem
Rem purge_b2b_oracle.sql
Rem
Rem Copyright (c) 2009, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_b2b_oracle.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem ekhor 05/06/10 - Created
Rem
CREATE OR REPLACE PACKAGE soa_b2b
AS
PROCEDURE deleteComponentInstances ( p_id_table IN VARCHAR2);
PROCEDURE pruneOpenECIDs ( p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP);
FUNCTION deleteNoCompositeIdInstances(p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER)RETURN BOOLEAN;
PROCEDURE createTempTables(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP);
FUNCTION deleteComponentInstances(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,
write_file utl_file.file_type default null) RETURN BOOLEAN;
FUNCTION isComponentPartitioned RETURN BOOLEAN;
END soa_b2b;
/
CREATE OR REPLACE
PACKAGE body soa_b2b
AS
--@@b2b_pruneOpenCompositeIDs.sql
/*
*
*
* pruneOpenCompositeIDs.sql
*
* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* pruneOpenCompositeIDs.sql - B2B purge prune script
*
*
* DESCRIPTION
* This script is used to prune a list of composite instance ids passed in
* an idTable. B2B uses following criteria to identify a non-purgeable instance :
* - state not in MSG_COMPLETE or MSG_ERROR
*
* Parameters:
* p_older_than TIMESTAMP : Not used for B2B
* p_id_table : this is the temp table with purge able composite instance ids
* p_prune_table : Add to this table the open instances
*
*/
PROCEDURE pruneOpenECIDs (
p_id_table IN VARCHAR2,
p_prune_table IN VARCHAR2,
p_older_than TIMESTAMP)
AS
v_stmt VARCHAR2(2000);
BEGIN
IF p_prune_table is not null then
v_stmt := 'insert into ' || p_prune_table || ' SELECT comp.ECID
FROM b2b_app_message am, b2b_business_message bm,' || p_id_table || ' comp
WHERE am.FABRIC_ECID = comp.ECID
AND (bm.state != ''MSG_COMPLETE'' AND bm.state != ''MSG_ERROR'')
AND bm.app_message = am.id';
EXECUTE immediate v_stmt;
debug_purge(p_prune_table, 'Inserted ');
END IF;
v_stmt := 'DELETE FROM ' || p_id_table || ' comp WHERE EXISTS ' ||
' (SELECT 1 FROM b2b_app_message am, b2b_business_message bm WHERE am.FABRIC_ECID = comp.ECID ' ||
' AND (bm.state != ''MSG_COMPLETE'' AND bm.state != ''MSG_ERROR'') AND bm.app_message = am.id)';
EXECUTE immediate v_stmt;
debug_purge(p_id_table);
COMMIT;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END pruneOpenECIDs;
--@@b2b_deleteComponentInstances.sql
/*
*
* deleteComponentInstances.sql
*
* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteComponentInstances.sql - BPEL purge script
*
*
* DESCRIPTION
* This script is used to purge BPEL tables provided with a list of composite instance ids
* in an idTable.
*
* For a user with multi cpu machine, usage of parallel dml may help. Add this to master script.
* execute immediate 'alter session enable parallel dml';
*
*/
PROCEDURE truncate_temp_tables
AS
BEGIN
--before starting clean up temp tables
EXECUTE IMMEDIATE 'TRUNCATE TABLE b2b_purge_temp_bm';
EXECUTE IMMEDIATE 'TRUNCATE TABLE b2b_purge_temp_wm';
EXECUTE IMMEDIATE 'TRUNCATE TABLE b2b_purge_temp_am';
END truncate_temp_tables;
/**
* procedure purge_b2b_tables
*
* delete b2b tables
*/
PROCEDURE purge_b2b_tables
AS
CURSOR b2b_cur IS
select bm_message_id, bm_payload_storage, bm_attachment_storage, bm_native_data_storage, bm_translated_data_storage, wm_packed_message, wm_payload_storage, am_payload, am_attachment_storage
from b2b_purge_temp_bm ;
TYPE bm_id_type IS TABLE OF b2b_purge_temp_bm.bm_message_id%TYPE;
bm_id bm_id_type;
TYPE bm_ps_type IS TABLE OF b2b_purge_temp_bm.bm_payload_storage%TYPE;
bm_ps bm_ps_type;
TYPE bm_as_type IS TABLE OF b2b_purge_temp_bm.bm_attachment_storage%TYPE;
bm_as bm_as_type;
TYPE bm_ns_type IS TABLE OF b2b_purge_temp_bm.bm_native_data_storage%TYPE;
bm_ns bm_ns_type;
TYPE bm_ts_type IS TABLE OF b2b_purge_temp_bm.bm_translated_data_storage%TYPE;
bm_ts bm_ts_type;
TYPE wm_pm_type IS TABLE OF b2b_purge_temp_bm.wm_packed_message%TYPE;
wm_pm wm_pm_type;
TYPE wm_ps_type IS TABLE OF b2b_purge_temp_bm.wm_payload_storage%TYPE;
wm_ps wm_ps_type;
TYPE am_p_type IS TABLE OF b2b_purge_temp_bm.am_payload%TYPE;
am_p am_p_type;
TYPE am_as_type IS TABLE OF b2b_purge_temp_bm.am_attachment_storage%TYPE;
am_as am_as_type;
BEGIN
OPEN b2b_cur;
FETCH b2b_cur BULK COLLECT into bm_id, bm_ps, bm_as, bm_ns, bm_ts, wm_pm, wm_ps, am_p, am_as;
dbms_output.put_line('TOTAL BM_ID = ' || bm_id.count());
FORALL i IN bm_ps.first .. bm_ps.last
DELETE FROM b2b_data_storage WHERE id = bm_ps(i);
FORALL i IN bm_as.first .. bm_as.last
DELETE FROM b2b_data_storage WHERE id = bm_as(i);
FORALL i IN bm_ns.first .. bm_ns.last
DELETE FROM b2b_data_storage WHERE id = bm_ns(i);
FORALL i IN bm_ts.first .. bm_ts.last
DELETE FROM b2b_data_storage WHERE id = bm_ts(i);
FORALL i IN wm_pm.first .. wm_pm.last
DELETE FROM b2b_data_storage WHERE id = wm_pm(i);
FORALL i IN wm_ps.first .. wm_ps.last
DELETE FROM b2b_data_storage WHERE id = wm_ps(i);
FORALL i IN am_p.first .. am_p.last
DELETE FROM b2b_data_storage WHERE id = am_p(i);
FORALL i IN am_as.first .. am_as.last
DELETE FROM b2b_data_storage WHERE id = am_as(i);
CLOSE b2b_cur;
EXECUTE IMMEDIATE 'TRUNCATE TABLE b2b_purge_temp_bm';
COMMIT;
END purge_b2b_tables;
/*
* Procedure : deleteComponentInstances
*
* Description:
* This procedure is provided a table with composite instance ids that are purge able.
*
* Parameters:
* idTable : Name of the id table that contains list of purge able composite instance IDs
*
*/
PROCEDURE deleteComponentInstances(p_id_table IN VARCHAR2)
AS
v_stmt VARCHAR2(2000);
BEGIN
truncate_temp_tables();
v_stmt := 'insert into b2b_purge_temp_bm(bm_id, bm_message_id, bm_payload_storage, bm_attachment_storage, bm_native_data_storage, bm_translated_data_storage, wm_packed_message, wm_payload_storage, am_payload, am_attachment_storage, ext_bm_id, am_id, wm_id) SELECT bm.id, bm.message_id, bm.payload_storage, bm.attachment_storage, bm.native_data_storage,
bm.translated_data_storage,
wm.packed_message, wm.payload_storage,
am.payload, am.attachment_storage, ext.id, am.id, wm.id
FROM b2b_business_message bm, b2b_wire_message wm, b2b_app_message am, b2b_ext_business_message ext, ' || p_id_table || ' temp_table
WHERE am.fabric_ecid = temp_table.ECID
AND bm.wire_message = wm.id (+)
AND bm.ext_business_message = ext.id (+)
AND bm.app_message = am.id;';
EXECUTE immediate v_stmt;
debug_purge('b2b_purge_temp_bm', 'Inserted = ');
purge_b2b_tables();
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteComponentInstances;
--@@b2b_deleteNoCompositeIdInstances.sql
/*
*
*
* deleteNoCompositeIdInstances.sql
*
* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteNoCompositeIdInstances.sql - B2B purge script used to purge B2B tables
* that do not have a composite instance Id
*
*
* DESCRIPTION
* This script is used to purge B2B tables rows that are created with instanace tracking off
*
* Parameters:
* p_older_than Not used by B2B
* p_rownum Batch size, purge in batch size
*
*
*/
/*
* Procedure: deleteNoCompositeIdInstances
*
*
*/
FUNCTION deleteNoCompositeIdInstances( p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER)
RETURN BOOLEAN
AS
v_stmt VARCHAR2(2000);
v_count NUMBER;
BEGIN
truncate_temp_tables();
v_stmt := 'insert into b2b_purge_temp_bm(bm_id, bm_message_id, bm_payload_storage, bm_attachment_storage, bm_native_data_storage, bm_translated_data_storage, wm_packed_message, wm_payload_storage, am_payload, am_attachment_storage, ext_bm_id, am_id, wm_id) SELECT bm.id, bm.message_id, bm.payload_storage, bm.attachment_storage, bm.native_data_storage,
bm.translated_data_storage,
wm.packed_message, wm.payload_storage,
am.payload, am.attachment_storage, ext.id, am.id, wm.id
FROM b2b_business_message bm, b2b_wire_message wm, b2b_app_message am, b2b_ext_business_message ext
WHERE am.FABRIC_COMPOSITE_INSTANCE_ID is null
AND bm.CPST_INST_CREATED_TIME BETWEEN ' || p_min_creation_date ||
' AND ' || p_max_creation_date || '
AND bm.wire_message = wm.id (+)
AND bm.ext_business_message = ext.id (+)
AND bm.app_message = am.id;
AND ROWNUM <= ' || p_rownum;
EXECUTE immediate v_stmt;
debug_purge('b2b_purge_temp_bm', 'Inserted = ');
if SQL%ROWCOUNT = 0 then
log_info('No B2B instances found with null composite instance ids');
RETURN FALSE;
end if;
purge_b2b_tables();
return TRUE;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteNoCompositeIdInstances ;
--@@b2b_deleteComponentInstancesDOP.sql
/*
*
* deleteComponentInstancesDOP.sql
*
* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteComponentInstancesDOP.sql - BPEL purge script
*
*
* DESCRIPTION
* This script is used to purge b2B tables provided with a list of composite instance ids
* in an idTable in a threaded mode.
*
*
*/
/**
* Procedure: createTempTables
*
* Description
* Purpose of this procedure is to create all the required temp tables that
* can be used to purge all of B2B data that matches the purge criteria.
*
* p_id_table: this is ths table that contrains the purge able composite insance id
* identified by the calling script.
* p_max_count: Max number of rows to attempt purge
* p_min_creation_date: min date range to attempt purge
* p_max_creation_date: max date range to attempt purge
* p_older_than: Not used by B2B
*
*/
PROCEDURE createTempTables(p_id_table IN VARCHAR2,
p_max_count IN INTEGER,
p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP)
AS
v_stmt VARCHAR2(2000);
v_rownum INTEGER;
BEGIN
truncate_temp_tables() ;
v_stmt := 'insert into b2b_purge_temp_bm(bm_id, bm_message_id, bm_payload_storage, bm_attachment_storage, bm_native_data_storage, bm_translated_data_storage, wm_packed_message, wm_payload_storage, am_payload, am_attachment_storage, ext_bm_id, am_id, wm_id) SELECT bm.id, bm.message_id, bm.payload_storage, bm.attachment_storage, bm.native_data_storage,
bm.translated_data_storage,
wm.packed_message, wm.payload_storage,
am.payload, am.attachment_storage, ext.id, am.id, wm.id
FROM b2b_business_message bm, b2b_wire_message wm, b2b_app_message am, b2b_ext_business_message ext, ' || p_id_table || ' temp_table
WHERE am.fabric_ecid = temp_table.ECID
AND bm.wire_message = wm.id (+)
AND bm.ext_business_message = ext.id (+)
AND bm.app_message = am.id
AND ROWNUM <= ' || p_max_count;
EXECUTE immediate v_stmt;
debug_purge('b2b_purge_temp_bm', 'Inserted =');
v_rownum := p_max_count - SQL%ROWCOUNT ;
IF v_rownum > 0 THEN
v_stmt := 'insert into b2b_purge_temp_bm(bm_id, bm_message_id, bm_payload_storage, bm_attachment_storage, bm_native_data_storage, bm_translated_data_storage, wm_packed_message, wm_payload_storage, am_payload, am_attachment_storage, ext_bm_id, am_id, wm_id) SELECT bm.id, bm.message_id, bm.payload_storage, bm.attachment_storage, bm.native_data_storage,
bm.translated_data_storage,
wm.packed_message, wm.payload_storage,
am.payload, am.attachment_storage, ext.id, am.id, wm.id
FROM b2b_business_message bm, b2b_wire_message wm, b2b_app_message am, b2b_ext_business_message ext
WHERE am.FABRIC_COMPOSITE_INSTANCE_ID is null
AND bm.CPST_INST_CREATED_TIME BETWEEN ' || p_min_creation_date ||
' AND ' || p_max_creation_date || '
AND (bm.state = ''MSG_COMPLETE'' OR bm.state = ''MSG_ERROR'')
AND bm.wire_message = wm.id (+)
AND bm.ext_business_message = ext.id (+)
AND bm.app_message = am.id (+)
AND ROWNUM <= ' || v_rownum;
EXECUTE immediate v_stmt;
debug_purge('b2b_purge_temp_bm', 'Inserted = ');
COMMIT;
END IF;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END createTempTables;
/*
* Procedure: deleteComponentInstaces
*
* Note: You need to obtain EXECUTE privilege to run this procedure.
* GRANT EXECUTE ON dbms_lock TO user_name
*
*/
FUNCTION deleteComponentInstances(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file utl_file.file_type default NULL)
RETURN BOOLEAN
AS
CURSOR b2b_cur IS
select bm_message_id, bm_payload_storage, bm_attachment_storage, bm_native_data_storage, bm_translated_data_storage, wm_packed_message, wm_payload_storage, am_payload, am_attachment_storage from b2b_purge_temp_bm;
TYPE bm_id_type IS TABLE OF b2b_purge_temp_bm.bm_message_id%TYPE;
bm_id bm_id_type;
TYPE bm_ps_type IS TABLE OF b2b_purge_temp_bm.bm_payload_storage%TYPE;
bm_ps bm_ps_type;
TYPE bm_as_type IS TABLE OF b2b_purge_temp_bm.bm_attachment_storage%TYPE;
bm_as bm_as_type;
TYPE bm_ns_type IS TABLE OF b2b_purge_temp_bm.bm_native_data_storage%TYPE;
bm_ns bm_ns_type;
TYPE bm_ts_type IS TABLE OF b2b_purge_temp_bm.bm_translated_data_storage%TYPE;
bm_ts bm_ts_type;
TYPE wm_pm_type IS TABLE OF b2b_purge_temp_bm.wm_packed_message%TYPE;
wm_pm wm_pm_type;
TYPE wm_ps_type IS TABLE OF b2b_purge_temp_bm.wm_payload_storage%TYPE;
wm_ps wm_ps_type;
TYPE am_p_type IS TABLE OF b2b_purge_temp_bm.am_payload%TYPE;
am_p am_p_type;
TYPE am_as_type IS TABLE OF b2b_purge_temp_bm.am_attachment_storage%TYPE;
am_as am_as_type;
v_stmt VARCHAR2(2000);
v_deleted boolean:=true;
v_counter NUMBER := 1;
f1_flag boolean:=true;
v_stoptime date :=p_stop_time;
BEGIN
v_deleted := true;
OPEN b2b_cur;
while v_deleted LOOP -- this loop is for keeping transactions short
v_deleted := false;
log_info('Begin B2B purge loop ' || v_counter || ' for thread = ' || p_thread,write_file);
v_stmt := 'Purge B2B_BUSINESS_MESSAGE' ;
IF f1_flag then
f1_flag:=false;
FETCH b2b_cur BULK COLLECT INTO bm_id, bm_ps, bm_as, bm_ns, bm_ts, wm_pm, wm_ps, am_p, am_as LIMIT p_batch_size;
dbms_output.put_line('TOTAL DOP BM_ID = ' || bm_id.count());
FORALL i IN bm_ps.first .. bm_ps.last
DELETE FROM b2b_data_storage WHERE id = bm_ps(i) AND mod (dbms_utility.get_hash_value(bm_ps(i),0,p_dop), p_dop)=p_thread;
IF SQL%FOUND THEN
v_deleted := true;
f1_flag:=true;
END IF;
FORALL i IN bm_as.first .. bm_as.last
DELETE FROM b2b_data_storage WHERE id = bm_as(i) AND mod (dbms_utility.get_hash_value(bm_as(i),0,p_dop), p_dop)=p_thread;
FORALL i IN bm_ns.first .. bm_ns.last
DELETE FROM b2b_data_storage WHERE id = bm_ns(i) AND mod (dbms_utility.get_hash_value(bm_ns(i),0,p_dop), p_dop)=p_thread;
FORALL i IN bm_ts.first .. bm_ts.last
DELETE FROM b2b_data_storage WHERE id = bm_ts(i) AND mod (dbms_utility.get_hash_value(bm_ts(i),0,p_dop), p_dop)=p_thread;
FORALL i IN wm_pm.first .. wm_pm.last
DELETE FROM b2b_data_storage WHERE id = wm_pm(i) AND mod (dbms_utility.get_hash_value(wm_pm(i),0,p_dop), p_dop)=p_thread;
FORALL i IN wm_ps.first .. wm_ps.last
DELETE FROM b2b_data_storage WHERE id = wm_ps(i) AND mod (dbms_utility.get_hash_value(wm_ps(i),0,p_dop), p_dop)=p_thread;
FORALL i IN am_p.first .. am_p.last
DELETE FROM b2b_data_storage WHERE id = am_p(i) AND mod (dbms_utility.get_hash_value(am_p(i),0,p_dop), p_dop)=p_thread;
FORALL i IN am_as.first .. am_as.last
DELETE FROM b2b_data_storage WHERE id = am_as(i) AND mod (dbms_utility.get_hash_value(am_as(i),0,p_dop), p_dop)=p_thread;
END IF;
debug_purge('b2b_purge_temp_bm',null,write_file);
COMMIT;
v_counter := v_counter + 1;
-- exit loop if out of ime
IF (sysdate >= v_stoptime) THEN
v_deleted := FALSE;
CLOSE b2b_cur;
return FALSE ;
END IF;
END LOOP;
CLOSE b2b_cur;
return TRUE;
EXCEPTION
when others then
log_error(v_stmt, write_file);
CLOSE b2b_cur;
raise;
END deleteComponentInstances;
--@@b2b_isComponentPartitioned.sql
function isComponentPartitioned return boolean is
begin
return is_table_partitioned('B2B_BUSINESS_MESSAGE','CPST_INST_CREATED_TIME');
end isComponentPartitioned;
END soa_b2b;
/
--show errors;
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--@@soa/purge_soa_oracle.sql
--SET LINESIZE 128
Rem
Rem
Rem Copyright (c) 2010, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_soa_oracle.sql - Fabric purge script
Rem
Rem
Rem DESCRIPTION
Rem This script is used to purge all non-running, non-recovery-required
Rem composite instances based on a given time range
Rem
BEGIN
IF table_exist('ECID_PURGE') THEN
EXECUTE IMMEDIATE 'DROP TABLE ECID_PURGE';
END IF;
IF table_exist('PRUNE_RUNNING_INSTS') THEN
EXECUTE IMMEDIATE 'DROP TABLE PRUNE_RUNNING_INSTS';
END IF;
EXECUTE IMMEDIATE 'CREATE TABLE prune_running_insts (ecid varchar2(100))';
EXECUTE IMMEDIATE 'CREATE TABLE ecid_purge (ecid varchar2(100))';
IF not table_exist('JOB_FLOW_CONTROL') THEN
EXECUTE IMMEDIATE 'CREATE TABLE job_flow_control(job_thread number)';
END IF;
END;
/
create or replace package soa as
procedure delete_instances ( min_creation_date in timestamp,
max_creation_date in timestamp,
batch_size in integer default 20000,
max_runtime in integer default 60,
retention_period in timestamp default null,
purge_partitioned_component in boolean default false,
composite_name in varchar2 default null,
composite_revision in varchar2 default null,
soa_partition_name in varchar2 default null
);
procedure delete_instances_in_parallel (
min_creation_date in timestamp,
max_creation_date in timestamp,
batch_size in integer default 20000,
max_runtime in integer default 60,
retention_period in timestamp default null,
DOP in integer default 4,
max_count integer default 1000000,
purge_partitioned_component in boolean default false,
composite_name in varchar2 default null,
composite_revision in varchar2 default null,
soa_partition_name in varchar2 default null
);
procedure delete_insts_in_parallel_job(DOP in number,
thread_num in number,
batch_size in number,
p_stoptime in varchar2,
purge_partitioned_component in char
);
end soa;
/
create or replace package body soa as
--@@delete_instances.sql
/*
Rem
Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem deleteInstances.sql <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem SOA Purge
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem sanjain 04/29/10 - Enabling Mediator purge
Rem
*/
function getComponentPartitionInfo return component_partition_info is
componentPartInfo component_partition_info ;
begin
log_info('checking for partitions');
componentPartInfo := component_partition_info();
if soa_fabric.isComponentPartitioned then
componentPartInfo.fabricPartitioned := 'Y';
else
componentPartInfo.fabricPartitioned := 'N';
end if;
if soa_orabpel.isComponentPartitioned then
componentPartInfo.bpelPartitioned := 'Y';
else
componentPartInfo.bpelPartitioned := 'N';
end if;
if soa_mediator.isComponentPartitioned then
componentPartInfo.mediatorPartitioned := 'Y';
else
componentPartInfo.mediatorPartitioned := 'N';
end if;
if soa_b2b.isComponentPartitioned then
componentPartInfo.b2bPartitioned := 'Y';
else
componentPartInfo.b2bPartitioned := 'N';
end if;
if soa_workflow.isComponentPartitioned then
componentPartInfo.workflowPartitioned := 'Y';
else
componentPartInfo.workflowPartitioned := 'N';
end if;
if soa_decision.isComponentPartitioned then
componentPartInfo.decisionPartitioned := 'Y';
else
componentPartInfo.decisionPartitioned := 'N';
end if;
log_info('done checking for partitions');
return componentPartInfo;
end;
function make_composite_dn(soa_partition_name in varchar2, composite_name in varchar2, composite_revision in varchar2) return varchar2 is
composite_dn varchar2(500);
begin
if composite_name is not null then
composite_dn := composite_name||'!';
end if;
if composite_name is not null and composite_revision is not null then
composite_dn := composite_dn||composite_revision;
end if;
if soa_partition_name is not null then
composite_dn := soa_partition_name || '/' || composite_dn ;
end if;
if soa_partition_name is null and composite_dn is not null then
composite_dn := 'default/'||composite_dn;
end if;
return composite_dn;
end make_composite_dn;
procedure pruneOpenECIDs (purge_id_table in varchar2,prune_running_insts_table in varchar2, retention_period in timestamp) is
begin
soa_orabpel.pruneOpenECIDs (purge_id_table,prune_running_insts_table,retention_period);
soa_mediator.pruneOpenECIDs(purge_id_table,prune_running_insts_table,retention_period);
soa_workflow.pruneOpenECIDs(purge_id_table,prune_running_insts_table,retention_period);
soa_decision.pruneOpenECIDs (purge_id_table,prune_running_insts_table,retention_period);
end pruneOpenECIDs;
function deleteNoCompositeIdInstances(min_created_date in timestamp,
max_created_date in timestamp,
retention_period in timestamp,
batch_size in integer,
purge_partitioned_component in boolean,
componentPartInfo in component_partition_info,
stoptime in date,
composite_dn in varchar2,
soa_partition_name in varchar2,
composite_name in varchar2,
composite_revision in varchar2
) return boolean is
total_rows integer := 0;
state integer;
more_rows_to_delete_all boolean := false;
more_rows_to_delete boolean;
begin
IF (sysdate >= stoptime) THEN
return false;
END IF;
if purge_partitioned_component = true OR componentPartInfo.bpelPartitioned='N' then
log_info('calling soa_orabpel.deleteNoCompositeIdInstances');
if soa_orabpel.deleteNoCompositeIdInstances( min_created_date,
max_created_date,
retention_period,
batch_size,
soa_partition_name,
composite_name,
composite_revision) then
more_rows_to_delete:=true;
end if;
log_info('completed soa_orabpel.deleteNoCompositeIdInstances');
end if;
IF (sysdate >= stoptime) THEN
return false;
END IF;
if purge_partitioned_component = true OR componentPartInfo.workflowPartitioned='N' then
log_info('calling workflow.deleteNoCompositeIdInstances');
if soa_workflow.deleteNoCompositeIdInstances( min_created_date,
max_created_date,
retention_period,
batch_size,
composite_dn) then
more_rows_to_delete:=true;
end if;
log_info('completed workflow.deleteNoCompositeIdInstances');
end if;
IF (sysdate >= stoptime) THEN
return false;
END IF;
if purge_partitioned_component = true OR componentPartInfo.mediatorPartitioned='N'then
log_info('calling mediator.deleteNoCompositeIdInstances');
if soa_mediator.deleteNoCompositeIdInstances( min_created_date,
max_created_date,
retention_period,
batch_size,
composite_dn ) then
more_rows_to_delete:=true;
end if;
log_info('completed mediator.deleteNoCompositeIdInstances');
end if;
IF (sysdate >= stoptime) THEN
return false;
END IF;
if purge_partitioned_component = true OR componentPartInfo.decisionPartitioned='N' then
log_info('calling decision.deleteNoCompositeIdInstances');
if soa_decision.deleteNoCompositeIdInstances( min_created_date,
max_created_date,
retention_period,
batch_size,
composite_dn) then
more_rows_to_delete:=true;
end if;
log_info('completed decision.deleteNoCompositeIdInstances');
end if;
IF (sysdate >= stoptime) THEN
return false;
END IF;
if purge_partitioned_component = true OR componentPartInfo.fabricPartitioned='N' then
log_info('calling fabric.deleteNoCompositeIdInstances');
if soa_fabric.deleteNoCompositeIdInstances(min_created_date,
max_created_date,retention_period,
batch_size,
composite_dn
) then
more_rows_to_delete:=true;
end if;
log_info('completed fabric.deleteNoCompositeIdInstances');
end if;
return more_rows_to_delete;
exception
when others then
log_error('ERROR (deleteNoCompositeIdInstances)');
rollback;
raise;
end deleteNoCompositeIdInstances;
/**
* procedure delete_composite_instances
*
* Deletes the composite instances and all the associated rows in other fabric tables that
* reference this composite instance directly or indirectly.
*/
procedure delete_composite_instances(instance_id_table in varchar2,
purge_partitioned_component in boolean,
componentPartInfo in component_partition_info,
stoptime in date
) is
begin
IF (sysdate >= stoptime) THEN
return;
END IF;
if purge_partitioned_component = true OR componentPartInfo.bpelPartitioned='N'then
log_info('calling soa_orabpel.deleteComponentInstances');
soa_orabpel.deleteComponentInstances(instance_id_table);
log_info('completed soa_orabpel.deleteComponentInstances');
end if;
IF (sysdate >= stoptime) THEN
return;
END IF;
if purge_partitioned_component = true OR componentPartInfo.workflowPartitioned='N' then
log_info('calling workflow.deleteComponentInstances');
soa_workflow.deleteComponentInstances(instance_id_table);
log_info('completed workflow.deleteComponentInstances');
end if;
IF (sysdate >= stoptime) THEN
return;
END IF;
if purge_partitioned_component = true OR componentPartInfo.mediatorPartitioned='N' then
log_info('calling mediator.deleteComponentInstances');
soa_mediator.deleteComponentInstances(instance_id_table);
log_info('completed mediator.deleteComponentInstances');
end if;
IF (sysdate >= stoptime) THEN
return;
END IF;
if purge_partitioned_component = true OR componentPartInfo.decisionPartitioned='N' then
log_info('calling decision.deleteComponentInstances');
soa_decision.deleteComponentInstances(instance_id_table);
log_info('completed decision.deleteComponentInstances');
end if;
IF (sysdate >= stoptime) THEN
return;
END IF;
if purge_partitioned_component = true OR componentPartInfo.fabricPartitioned='N' then
log_info('calling fabric.deleteComponentInstances');
soa_fabric.deleteCompositeInstances(instance_id_table);
log_info('completed fabric.deleteComponentInstances');
end if;
IF (sysdate >= stoptime) THEN
return;
END IF;
exception
when others then
log_error('ERROR (delete_composite_instances)');
rollback;
raise;
end delete_composite_instances;
procedure delete_instances ( min_creation_date in timestamp,
max_creation_date in timestamp,
batch_size in integer default 20000,
max_runtime in integer default 60,
retention_period in timestamp default null,
purge_partitioned_component in boolean default false,
composite_name in varchar2 default null,
composite_revision in varchar2 default null,
soa_partition_name in varchar2 default null
) is
composite_id_sql varchar2(4000);
purge_id_table varchar2(100) := 'ecid_purge';
stoptime date := sysdate + NVL(max_runtime,24*60)/(24*60);
total_rows integer;
total_rows_after_pruning integer;
orphaned_loop BOOLEAN := TRUE;
non_orphaned_loop BOOLEAN:=TRUE;
iterationCount integer :=1;
deletingOrphanedInstance boolean;
more_rows_to_delete boolean := false;
prune_running_insts_table varchar2(100) :='prune_running_insts';
componentPartInfo component_partition_info;
v_retention_period timestamp := NVL(retention_period,max_creation_date);
composite_dn varchar2(500);
begin
log_info('procedure delete_instances');
log_info('time check');
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'));
log_info('stoptime = ' ||TO_CHAR( stoptime,'DD/MON/YYYY:HH24/MI'));
IF (sysdate >= stoptime) THEN
non_orphaned_loop := FALSE;
orphaned_loop := FALSE;
END IF;
execute immediate 'truncate table ' || prune_running_insts_table;
componentPartInfo := getComponentPartitionInfo();
composite_dn := make_composite_dn(soa_partition_name, composite_name, composite_revision);
log_info('composite_dn = ' || composite_dn);
WHILE orphaned_loop = TRUE OR non_orphaned_loop = TRUE LOOP
log_info('loop count = ' || iterationCount);
deletingOrphanedInstance := (mod (iterationCount,2)=0);
IF deletingOrphanedInstance = false and non_orphaned_loop = true then
log_info('deleting non-orphaned instances');
--select ECId from composite_instance where ( bitand(state,127)=1 or
----bitand(state,6)=2 or bitand(state,16)=16 or bitand(state,64)=64 or bitand(state,127)=32)...... and ECId not in (select ECId from prune_running_insts ) and rownum <=batch_size;
composite_id_sql := ' select unique ecid from composite_instance where (' ;
--states COMPLETED_SUCCESSFULLY, FAULTED, TERMINATED_BY_USER, STALE,STATE_UNKNOWN
composite_id_sql := composite_id_sql || ' bitand(state,127)=1 or ';
composite_id_sql := composite_id_sql || 'bitand(state,6)=2 or bitand(state,16)=16 or ';
composite_id_sql := composite_id_sql || 'bitand(state,64)=64 or bitand(state,127)=32) ';
composite_id_sql := composite_id_sql || 'and created_time >= ' || '''' || min_creation_date || '''';
composite_id_sql := composite_id_sql || ' and created_time <= ' || '''' || max_creation_date || '''';
if composite_dn is not null then
composite_id_sql := composite_id_sql || ' and composite_dn like ''' || composite_dn || '%''';
end if;
composite_id_sql := composite_id_sql || ' and ecid not in (select ecid from prune_running_insts ) ';
composite_id_sql := composite_id_sql || 'and rownum <= ' || batch_size ;
composite_id_sql := 'insert into '||purge_id_table || composite_id_sql;
execute immediate 'truncate table ' || purge_id_table;
execute immediate composite_id_sql;
total_rows := SQL%ROWCOUNT;
debug_purge(purge_id_table,'Inserted = ');
commit;
log_info('total rows before pruning ' || total_rows);
IF total_rows = 0 THEN
non_orphaned_loop := FALSE ;
END IF ;
log_info('calling pruneOpenECIDs ');
pruneOpenECIDs (purge_id_table,prune_running_insts_table,v_retention_period);
log_info('finished pruneOpenECIDs ');
SELECT count(*) INTO total_rows_after_pruning FROM ecid_purge;
log_info('total_rows_after_pruning ' || total_rows_after_pruning);
IF purge_partitioned_component = false AND componentPartInfo.fabricPartitioned='Y' then
-- since composite instances wouldnt be purged we add the processed ecids to prune_running_inst table
log_info('inserting processed ecids into prune_running_insts');
insert into prune_running_insts select ecid from ecid_purge;
debug_purge('prune_running_insts','inserted = ' );
commit;
END IF;
delete_composite_instances(purge_id_table,purge_partitioned_component,componentPartInfo, stoptime);
IF (sysdate >= stoptime) THEN
non_orphaned_loop := FALSE;
orphaned_loop := FALSE;
END IF;
END IF;
IF deletingOrphanedInstance = true and orphaned_loop = true then
log_info('deleting orphaned instances');
more_rows_to_delete := deleteNoCompositeIdInstances(min_creation_date,
max_creation_date, v_retention_period,batch_size,purge_partitioned_component,componentPartInfo,stoptime,
composite_dn, soa_partition_name, composite_name, composite_revision ) ;
orphaned_loop := more_rows_to_delete;
END IF;
commit;
log_info('time check');
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'));
log_info('stoptime = ' ||TO_CHAR( stoptime,'DD/MON/YYYY:HH24/MI'));
IF (sysdate >= stoptime) THEN
non_orphaned_loop := FALSE;
orphaned_loop := FALSE;
END IF;
iterationCount := iterationCount + 1;
END LOOP;
if(sysdate < stoptime and(purge_partitioned_component = true OR soa_fabric.isComponentPartitioned=false) ) then
soa_fabric.deleteRejectedMessages(min_creation_date,max_creation_date,batch_size,stoptime,purge_partitioned_component);
end if;
execute immediate 'truncate table ' || prune_running_insts_table;
log_info('delete_instances completed successfully');
EXCEPTION
when others then
log_error('ERROR(delete_instances) ');
end delete_instances;
--@@delete_insts_in_parallel_job.sql
/*
Rem
Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem delete_insts_in_parallel_job.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem sanjain 04/29/10 - Enabling Mediator purge
Rem
*/
procedure deleteComponentInstances(DOP in number,
thread_num in number,
batch_size in number,
v_stoptime in date,
write_file in utl_file.file_type,
purge_partitioned_component in char,
componentPartInfo in component_partition_info ) is
v_bool boolean;
begin
IF (sysdate < v_stoptime) THEN
log_info('thread: ' || thread_num || ' calling orabpel.deleteComponentInstances',write_file);
if purge_partitioned_component = 'Y' OR componentPartInfo.bpelPartitioned='N' then
v_bool := soa_orabpel.deleteComponentInstances(DOP,thread_num, batch_size,v_stoptime,write_file);
log_info('thread: ' || thread_num || ' finished processing bpel',write_file);
end if;
END IF;
IF (sysdate < v_stoptime) THEN
log_info('thread: ' || thread_num || ' calling workflow.deleteComponentInstances',write_file);
if purge_partitioned_component = 'Y' OR componentPartInfo.workflowPartitioned='N' then
v_bool:= soa_workflow.deleteComponentInstances(DOP,thread_num, batch_size,v_stoptime,write_file);
log_info('thread: ' || thread_num || ' finished processing workflow',write_file);
end if;
END IF;
IF (sysdate < v_stoptime) THEN
log_info('thread: ' || thread_num || ' calling mediator.deleteComponentInstances',write_file);
if purge_partitioned_component = 'Y' OR componentPartInfo.mediatorPartitioned='N' then
v_bool:= soa_mediator.deleteComponentInstances(DOP,thread_num, batch_size,v_stoptime,write_file);
log_info('thread: ' || thread_num || ' finished processing mediator',write_file);
end if;
END IF;
if(sysdate < v_stoptime) then
log_info('deleting composite instances',write_file);
if purge_partitioned_component = 'Y' OR componentPartInfo.fabricPartitioned='N' then
soa_fabric.delete_composite_inst_parallel(DOP,thread_num,batch_size,v_stoptime,write_file);
end if;
end if;
end deleteComponentInstances;
procedure delete_insts_in_parallel_job(DOP in number,
thread_num in number,
batch_size in number,
p_stoptime in varchar2,
purge_partitioned_component in char
) is
v_code NUMBER;
v_errm VARCHAR2(90);
v_stmt VARCHAR2(100);
v_sleeptime number;
v_jobflowsql varchar2(100);
v_stoptime date := to_date(p_stoptime,'DD/MON/YYYY:HH24/MI');
write_file utl_file.file_type;
componentPartInfo component_partition_info;
BEGIN
write_file := get_file(thread_num);
log_info( 'thread: ' || thread_num || ' sleep at start time',write_file);
-- to avoid starting all jobs at the same time causing concurrency issues we put a sleep here into
v_stmt := 'sleep at start of job';
v_sleeptime := 30*thread_num;
execute immediate 'begin dbms_lock.sleep(' || v_sleeptime || '); end;';
log_info('thread: ' || 'thread_num' || ' sleep over',write_file);
log_info('thread: ' || thread_num || ' time check',write_file);
log_info('time check');
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('v_stoptime = ' ||TO_CHAR( v_stoptime,'DD/MON/YYYY:HH24/MI'),write_file);
componentPartInfo := getComponentPartitionInfo();
deleteComponentInstances(DOP,thread_num, batch_size,v_stoptime,write_file,purge_partitioned_component,componentPartInfo);
log_info('time check');
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'));
log_info('thread: ' || thread_num || ' deleting from job_flow_control table',write_file);
v_jobflowsql := 'DELETE FROM job_flow_control WHERE job_thread =' || thread_num;
execute immediate v_jobflowsql;
commit;
close_file(write_file);
EXCEPTION
when others then
log_error('ERROR(delete_inst_in_parallel_job',write_file);
rollback;
v_jobflowsql := 'DELETE FROM job_flow_control WHERE job_thread ='|| thread_num;
execute immediate v_jobflowsql;
commit;
close_file(write_file);
raise;
END delete_insts_in_parallel_job;
--@@delete_instances_in_parallel.sql
/*
Rem
Rem
Rem delete_instances_in_parallel.sql
Rem
Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem delete_instances_in_parallel.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem ssudarsa 05/18/12 - Backport ssudarsa_bug-11832797_ps5 from
Rem st_pcbpel_11.1.1.4.0
Rem sanjain 04/29/10 - Enabling Mediator purge
Rem
*/
procedure createTempTables(
purge_id_table in varchar2,
max_count in integer,
min_creation_date in timestamp ,
max_creation_date in timestamp ,
retention_period in timestamp,
purge_partitioned_component in boolean,
componentPartInfo in component_partition_info,
composite_dn in varchar2,
composite_name in varchar2,
composite_revision in varchar2,
soa_partition_name in varchar2 ) is
begin
if purge_partitioned_component = true OR componentPartInfo.bpelPartitioned= 'N' then
soa_orabpel.createTempTables(purge_id_table,max_count,min_creation_date,max_creation_date,retention_period, composite_name, composite_revision, soa_partition_name);
end if;
if purge_partitioned_component = true OR componentPartInfo.mediatorPartitioned='N' then
soa_mediator.createTempTables(purge_id_table,max_count,min_creation_date,max_creation_date,retention_period, composite_dn);
end if;
if purge_partitioned_component = true OR componentPartInfo.workflowPartitioned='N' then
soa_workflow.createTempTables(purge_id_table,max_count,min_creation_date,max_creation_date,retention_period, composite_dn);
end if;
if purge_partitioned_component = true OR componentPartInfo.fabricPartitioned='N' then
soa_fabric.createTempTables(purge_id_table,max_count,min_creation_date,max_creation_date,retention_period, composite_dn);
end if;
end createTempTables;
procedure deleteNonParallelComponents(
purge_id_table in varchar2,
max_count in integer,
min_creation_date in timestamp ,
max_creation_date in timestamp ,
retention_period in timestamp,
purge_partitioned_component in boolean,
componentPartInfo in component_partition_info,
composite_dn in varchar2 ) is
begin
if purge_partitioned_component = true OR componentPartInfo.decisionPartitioned='N' then
soa_decision.deleteComponentInstances(purge_id_table,max_count,min_creation_date,max_creation_date,retention_period, composite_dn);
end if;
end deleteNonParallelComponents;
procedure delete_instances_in_parallel (
min_creation_date in timestamp,
max_creation_date in timestamp,
batch_size in integer default 20000,
max_runtime in integer default 60,
retention_period in timestamp default null,
DOP in integer default 4 ,
max_count in integer default 1000000,
purge_partitioned_component in boolean default false,
composite_name in varchar2 default null,
composite_revision in varchar2 default null,
soa_partition_name in varchar2 default null ) is
composite_id_sql varchar2(4000);
purge_id_table varchar2(100) := 'ecid_purge';
stoptime varchar2(100) := to_char(sysdate + NVL(max_runtime,24*60)/(24*60),'DD/MON/YYYY:HH24/MI');
total_rows integer;
v_thread number := 0;
v_jobname VARCHAR2(20);
v_jobsrunning number;
v_jobflowsql varchar2(100);
total_rows_after_pruning integer;
purge_partitioned_comp_char char;
prune_running_insts_table varchar2(100) :='prune_running_insts';
componentPartInfo component_partition_info;
v_retention_period timestamp := NVL(retention_period,max_creation_date);
composite_dn varchar2(500);
begin
--check whether any job is running or whether they shut down properly
log_info('checking for jobs running');
SELECT count(*) INTO v_jobsrunning FROM job_flow_control;
log_info('jobs running = ' || v_jobsrunning);
-- if not, raise an error and let the exception handling take care of it
IF v_jobsrunning != 0 THEN
raise_application_error (-20001,'Jobs still running or not shut down properly');
END IF;
if purge_partitioned_component = true then
purge_partitioned_comp_char := 'Y';
else
purge_partitioned_comp_char := 'N';
end if;
composite_dn := make_composite_dn(soa_partition_name, composite_name, composite_revision);
log_info('composite_dn = ' || composite_dn);
composite_id_sql := ' select unique ecid from composite_instance where (' ;
--states COMPLETED_SUCCESSFULLY, FAULTED, TERMINATED_BY_USER, STALE,STATE_UNKNOWN
composite_id_sql := composite_id_sql || ' bitand(state,127)=1 or ';
composite_id_sql := composite_id_sql || 'bitand(state,6)=2 or bitand(state,16)=16 or ';
composite_id_sql := composite_id_sql || 'bitand(state,64)=64 or bitand(state,127)=32) ';
composite_id_sql := composite_id_sql || 'and created_time >= ' || '''' || min_creation_date || '''';
composite_id_sql := composite_id_sql || ' and created_time <= ' || '''' || max_creation_date || '''';
if composite_dn is not null then
composite_id_sql := composite_id_sql || ' and composite_dn like ''' || composite_dn || '%''';
end if;
composite_id_sql := composite_id_sql || ' and rownum <= ' || max_count;
composite_id_sql := 'insert into '||purge_id_table || composite_id_sql;
execute immediate 'truncate table ' || purge_id_table;
execute immediate 'truncate table ' || prune_running_insts_table;
execute immediate composite_id_sql;
total_rows := SQL%ROWCOUNT;
debug_purge(purge_id_table, ' inserted = ');
commit;
log_info('total rows before pruning ' || total_rows);
log_info('calling pruneOpenECIDs ');
pruneOpenECIDs (purge_id_table,prune_running_insts_table,v_retention_period);
log_info('completed pruneOpenECIDs ');
SELECT count(*) INTO total_rows_after_pruning FROM ecid_purge;
log_info('total_rows_after_pruning ' || total_rows_after_pruning);
log_info('creating temp tables');
componentPartInfo := getComponentPartitionInfo();
createTempTables(purge_id_table,max_count,min_creation_date,max_creation_date,v_retention_period,purge_partitioned_component,componentPartInfo,composite_dn , composite_name,composite_revision,soa_partition_name);
log_info('completed creating temp tables');
-- purge small volume data components which would not warrant multi-threaded purge
log_info('purging small volume components');
deleteNonParallelComponents(purge_id_table,max_count,min_creation_date,max_creation_date,v_retention_period,purge_partitioned_component,componentPartInfo,composite_dn);
log_info('completed purging small volume components');
LOOP
-- exit loop when DOP jobs have been started
EXIT WHEN DOP=v_thread;
v_jobname := 'SOA_PURGE_'||v_thread;
v_jobflowsql := 'insert into job_flow_control values (' || v_thread||')';
execute immediate v_jobflowsql;
commit;
log_info('inserting v_thread into job_flow_control ');
log_info('spawning thread ' ||v_thread );
execute immediate 'BEGIN ' ||
'dbms_scheduler.create_job (''' ||v_jobname||''',''STORED_PROCEDURE'',''soa.delete_insts_in_parallel_job'', 5); '||
'dbms_scheduler.set_job_argument_value (''' ||v_jobname || ''',1,to_char(' ||DOP||')); ' ||
'dbms_scheduler.set_job_argument_value (''' || v_jobname ||''' ,2,to_char('||v_thread||')); '||
'dbms_scheduler.set_job_argument_value ('''|| v_jobname || ''',3,to_char('||batch_size||')); ' ||
'dbms_scheduler.set_job_argument_value (''' ||v_jobname || ''',4,'''||stoptime||'''); ' ||
'dbms_scheduler.set_job_argument_value (''' || v_jobname || ''',5,'''||purge_partitioned_comp_char||'''); ' ||
'DBMS_SCHEDULER.SET_ATTRIBUTE (
'''||v_jobname||''', ''logging_level'', DBMS_SCHEDULER.LOGGING_FULL); '||
'dbms_scheduler.enable ('''||v_jobname||'''); ' ||
' END; ';
v_thread := v_thread +1;
END LOOP;
log_info('thread spawning done');
EXCEPTION
when others then
log_error('ERROR(delete_instances_in_parallel');
raise;
end delete_instances_in_parallel;
end soa;
/
--show errors;
