Below script can also be found at $ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge location of application server. You just need to copy paste below script and run it using SQL Developer or similar software.
After running below script go to http://yogesh4984.blogspot.in/2012/12/purging-scripts-for-soa-11g-part-2.html for remaining scripts
Rem
Rem
Rem createschema_soainfra_oracle.sql
Rem
Rem Copyright (c) 2006, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_soa_all.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/29/10 - Adding mediator purge script
Rem ssudarsa 04/13/10 - Creaetd
Rem
--@@common/purge_common_oracle.sql
Rem
Rem $Header: pcbpel/admin/sqlutils/soa_purge/oracle/common/purge_common_oracle.sql /st_pcbpel_11.1.1.4.0/1 2010/07/30 04:57:14 ssudarsa Exp $
Rem
Rem purge_common_oracle.sql
Rem
Rem Copyright (c) 2009, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_common_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 ssudarsa 04/13/10 - Created
Rem
create or replace procedure write_line(line in varchar2, write_file in utl_file.file_type default null) is
begin
if utl_file.is_open(write_file)=false then
dbms_output.put_line(line);
else
utl_file.put_line (write_file,line);
end if;
end write_line;
/
--show errors;
CREATE OR REPLACE PROCEDURE debug_purge (table_name IN VARCHAR2, str IN VARCHAR2 default null,write_file in utl_file.file_type default null) AS
BEGIN
$IF $$debug_on $THEN
if str is NULL then
write_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' Number of rows in table ' || table_name || ' ' || ' purged is : ' || SQL%ROWCOUNT,write_file);
ELSE
write_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' Number of rows in table ' || table_name || ' ' || str || SQL%ROWCOUNT,write_file);
END IF;
$ELSE
null;
$END
END debug_purge;
/
--show errors;
create or replace procedure log_info(message in varchar2, write_file utl_file.file_type default null) is
begin
$IF $$debug_on $THEN
write_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') ||' : '||message,write_file);
$ELSE
null;
$END
end log_info;
/
--show errors;
create or replace procedure log_error(message in varchar2,write_file utl_file.file_type default null) is
begin
write_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') ||'
: '||message ||'. Error Code = '||SQLCODE||', Error Message = '||SQLERRM,write_file);
end log_error;
/
--show errors;
create or replace function is_table_partitioned(table_name in varchar2,
partition_column_name in varchar2) return boolean is
row_found integer := 0;
begin
select count(1) into row_found from user_part_key_columns where object_type ='TABLE' and name = table_name and column_name = partition_column_name;
if row_found = 1 then
return true;
else
return false;
end if;
end is_table_partitioned;
/
--show errors;
create or replace function partition_exists(table_name in varchar2,
partition_name in varchar2) return boolean is
row_found integer := 0;
tablename varchar2(100) := upper(table_name);
partitionname varchar2(100) := upper(partition_name);
begin
select count(1) into row_found from user_tab_partitions where table_name =tablename and partition_name = partitionname;
if row_found = 1 then
return true;
else
return false;
end if;
end partition_exists;
/
--show errors;
CREATE OR REPLACE FUNCTION table_exist( tablename IN VARCHAR2)
RETURN BOOLEAN
IS
val VARCHAR2(2) :='F';
val_T VARCHAR2(2) :='T';
BEGIN
SELECT DECODE(COUNT(*),0,'F','T')
INTO val
from USER_TABLES
WHERE table_name = tablename;
IF val = val_T THEN
RETURN true;
ELSE
RETURN false;
END IF;
END table_exist;
/
--show errors;
create or replace function get_file(thread_num in integer) return utl_file.file_type is
write_file utl_file.file_type;
SOA_PURGE_DIR_NAME varchar2(40) := 'SOA_PURGE_DIR';
filename varchar2(40) := 'SOA_PURGE_LOG_THREAD' || thread_num;
begin
write_file := utl_file.fopen (SOA_PURGE_DIR_NAME,filename, 'W');
return write_file;
EXCEPTION
when others then
return null;
end get_file;
/
--show errors;
create or replace procedure close_file(write_file in out utl_file.file_type) is
begin
utl_file.fclose(write_file);
end close_file;
/
--show errors;
create or replace function column_exists( p_table_name varchar2, p_column_name varchar2)
RETURN BOOLEAN
IS
val VARCHAR2(2) :='F';
val_T VARCHAR2(2) :='T';
BEGIN
SELECT DECODE(COUNT(*),0,'F','T') INTO val
FROM USER_TAB_COLUMNS WHERE table_name = p_table_name and column_name = p_column_name ;
IF val = val_T THEN
RETURN true;
ELSE
RETURN false;
END IF;
END column_exists;
/
--show errors;
create or replace procedure write_drop_partition_query(p_table_name varchar2,p_partition_name varchar2,write_file utl_file.file_type)
is
v_query varchar2(4000);
begin
v_query := 'ALTER TABLE ' || p_table_name || ' drop partition ' || p_partition_name ||' UPDATE GLOBAL INDEXES;';
write_line(v_query,write_file);
end write_drop_partition_query;
/
--show errors
create or replace procedure write_query_comments(comments varchar2, write_file utl_file.file_type)
is
begin
write_line('-- ' || comments,write_file );
end write_query_comments;
/
--show errors
create or replace type component_partition_info as object
(
fabricPartitioned char,
bpelPartitioned char,
mediatorPartitioned char,
b2bPartitioned char,
workflowPartitioned char,
decisionPartitioned char,
constructor function component_partition_info return self as result
);
/
--show errors;
create or replace type body component_partition_info as
constructor function component_partition_info return self as result is
begin
return;
end component_partition_info;
end;
/
--show errors;
--------------------------------------
--------------------------------------
-------------------------------------
--@@orabpel/purge_bpel_oracle.sql
Rem
Rem
Rem purge_bpel_oracle.sql
Rem
Rem Copyright (c) 2009, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_bpel_oracle.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Purges BPEL related tables.
Rem
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_CUBE_INSTANCE') THEN
EXECUTE IMMEDIATE 'DROP TABLE temp_cube_instance';
END IF;
IF table_exist('TEMP_DOCUMENT_CI_REF') THEN
EXECUTE IMMEDIATE 'DROP TABLE temp_document_ci_ref';
END IF;
IF table_exist('TEMP_DOCUMENT_DLV_MSG_REF') THEN
EXECUTE IMMEDIATE 'DROP TABLE temp_document_dlv_msg_ref';
END IF;
IF table_exists('TEMP_XML_DOCUMENT') THEN
EXECUTE IMMEDIATE 'DROP TABLE temp_xml_document';
END IF;
-- Create temporary tables.
EXECUTE IMMEDIATE 'CREATE TABLE temp_cube_instance
(
cikey INTEGER default ''0'',
ecid VARCHAR2(100) default null
)';
EXECUTE IMMEDIATE 'CREATE TABLE temp_document_ci_ref
(
document_id VARCHAR2(200),
cikey INTEGER default ''0'',
ecid VARCHAR2(100) default null
)';
EXECUTE IMMEDIATE 'CREATE TABLE temp_document_dlv_msg_ref
(
message_guid VARCHAR2( 50 ),
document_id VARCHAR2(200),
cikey INTEGER default ''0'',
ecid VARCHAR2(100) default null
)';
EXECUTE IMMEDIATE 'CREATE TABLE temp_xml_document
(
document_id VARCHAR2(200),
cikey INTEGER default ''0'',
ecid VARCHAR2(100) default null
)';
END;
/
CREATE OR REPLACE PACKAGE soa_orabpel
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,
soa_partition_name in varchar2,
composite_name in varchar2,
composite_revision 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_name in varchar2 ,
composite_revision in varchar2,
soa_partition_name 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_orabpel;
/
--show errors;
CREATE OR REPLACE
PACKAGE body soa_orabpel
AS
--@@orabpel_pruneOpenCompositeIDs.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 :
* - ci state is <5 (instance is still open)
* - ci.modify_date is in retention period
* - any recoverable invoke message exists
*
* 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 CUBE_INSTANCE ci, ' ||
p_id_table || ' comp WHERE comp.ECID = ci.ECID AND ( ci.STATE < 5 ' ||
' OR ci.MODIFY_DATE >= TO_TIMESTAMP(''RETENTION_PERIOD''))' ;
v_stmt := REPLACE(v_stmt,'RETENTION_PERIOD',p_older_than);
EXECUTE immediate v_stmt;
debug_purge(p_prune_table, 'Inserted (bpel) for open ci ');
--Recoverable invoke messages cannot be purged.These are in state 0 or 1
v_stmt := 'INSERT INTO ' || p_prune_table || ' SELECT dlv.ECID from DLV_MESSAGE dlv, ' ||
p_id_table || ' comp WHERE comp.ECID = dlv.ECID AND dlv.DLV_TYPE=1 AND dlv.STATE in (0,1)' ;
EXECUTE immediate v_stmt;
debug_purge(p_prune_table, 'Inserted (bpel) for dlv_message');
END IF;
/*
v_stmt := 'DELETE FROM ' || p_id_table || ' comp WHERE EXISTS ' ||
' (SELECT 1 FROM DLV_MESSAGE dlv WHERE dlv.ECID = comp.ECID AND dlv.DLV_TYPE=1 AND dlv.STATE in (0,1) )';
EXECUTE immediate v_stmt;
debug_purge(p_id_table, 'Deleted (bpel) for open ci');
v_stmt := 'DELETE FROM ' || p_id_table || ' comp WHERE EXISTS ' ||
' (SELECT 1 FROM CUBE_INSTANCE ci WHERE ci.ECID = comp.ECID ' ||
' AND (ci.STATE < 5 or ci.MODIFY_DATE >= TO_TIMESTAMP(''RETENTION_PERIOD'')) )';
v_stmt := REPLACE(v_stmt,'RETENTION_PERIOD',p_older_than);
*/
v_stmt := 'DELETE FROM ' || p_id_table || ' comp WHERE EXISTS ' ||
' (SELECT 1 FROM ' || p_prune_table || ' prune WHERE prune.ECID = comp.ECID )' ;
EXECUTE immediate v_stmt;
debug_purge(p_id_table, 'Deleted (bpel) for dlv_message');
COMMIT;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END pruneOpenECIDs;
--@@orabpel_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 temp_cube_instance';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_document_ci_ref';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_document_dlv_msg_ref';
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_xml_document';
END truncate_temp_tables;
/*
* Procedure: purge_bpel_tables
*
* Description
* This procedure purges following BPEL tables based on temp tables created in earlier step.
*
* HEADERS_PROPERTIES
* AG_INSTANCE
* TEST_DETAILS
* CUBE_SCOPE
* AUDIT_COUNTER
* AUDIT_TRAIL
* AUDIT_DETAILS
* CI_INDEXS
* WORK_ITEM
* WI_FAULT
* XML_DOCUMENT
* DOCUMENT_DLV_MSG_REF
* DOCUMENT_CI_REF
* DLV_MESSAGE
* DLV_SUBSCRIPTION
* CUBE_INSTANCE
*
* BPM_AUDIT_QUERY
* BPM_MEASUREMENT_ACTIONS
* BPM_MEASUREMENT_ACTION_EXCEPS
* BPM_CUBE_AUDITINSTANCE
* BPM_CUBE_TASKPERFORMANCE
* BPM_CUBE_PROCESSPERFORMANCE
*
*/
PROCEDURE purge_bpel_tables
AS
v_stmt VARCHAR2(2000);
BEGIN
--Create additional temp tables
v_stmt := 'Create temp_document_ci_ref' ;
INSERT INTO temp_document_ci_ref (document_id) SELECT document_id
FROM document_ci_ref d, temp_cube_instance t WHERE d.cikey = t.cikey;
debug_purge('temp_document_ci_ref', 'Inserted = ');
v_stmt := 'Create temp_document_dlv_msg_ref from dlv_message type = 1 and 2';
/*
INSERT INTO temp_document_dlv_msg_ref (message_guid,document_id) SELECT m.message_guid,ddmr.document_id
FROM document_dlv_msg_ref ddmr, dlv_message m , temp_cube_instance t
WHERE ddmr.message_guid = m.message_guid
AND m.state in (2,3) AND ( m.cikey = t.cikey OR m.cikey = 0 ) ;
*/
INSERT INTO temp_document_dlv_msg_ref (message_guid,document_id)
SELECT m.message_guid,ddmr.document_id
FROM document_dlv_msg_ref ddmr, dlv_message m , temp_cube_instance t
WHERE ddmr.message_guid = m.message_guid
AND m.state in (2,3) AND m.cikey = t.cikey ;
debug_purge('temp_document_dlv_msg_ref', 'Inserted = ');
-- Being purge of BPEL data
v_stmt := 'Purge HEADERS_PROPERTIES' ;
DELETE FROM HEADERS_PROPERTIES WHERE message_guid IN (SELECT message_guid FROM temp_document_dlv_msg_ref) ;
debug_purge('HEADERS_PROPERTIES');
COMMIT;
v_stmt := 'Purge AG_INSTANCE' ;
DELETE FROM AG_INSTANCE WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('AG_INSTANCE');
COMMIT;
v_stmt := 'Purge TEST_DETAILS' ;
DELETE FROM TEST_DETAILS WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('TEST_DETAILS');
COMMIT;
v_stmt := 'Purge CUBE_SCOPE' ;
DELETE FROM CUBE_SCOPE WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('CUBE_SCOPE');
COMMIT;
v_stmt := 'Purge AUDIT_COUNTER' ;
v_stmt:=' DELETE FROM AUDIT_COUNTER WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic)';
execute immediate v_stmt;
debug_purge('AUDIT_COUNTER');
COMMIT;
v_stmt := 'Purge AUDIT_TRAIL' ;
DELETE FROM AUDIT_TRAIL WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('AUDIT_TRAIL');
COMMIT;
v_stmt := 'Purge AUDIT_DETAILS' ;
DELETE FROM AUDIT_DETAILS WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('AUDIT_DETAILS');
COMMIT;
v_stmt := 'Purge CI_INDEXS' ;
DELETE FROM CI_INDEXES WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('CI_INDEXES');
COMMIT;
v_stmt := 'Purge WORK_ITEM' ;
DELETE FROM WORK_ITEM WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('WORK_ITEM');
COMMIT ;
v_stmt := 'Purge WI_FAULT' ;
DELETE FROM WI_FAULT WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('WI_FAULT');
COMMIT ;
v_stmt := 'Purge XML_DOCUMENT';
DELETE FROM XML_DOCUMENT d WHERE document_id in
( SELECT t1.document_id FROM temp_document_dlv_msg_ref t1
union
SELECT t2.document_id FROM temp_document_ci_ref t2
);
debug_purge('XML_DOCUMENT');
COMMIT ;
v_stmt := 'Purge DOCUMENT_DLV_MSG_REF' ;
DELETE FROM DOCUMENT_DLV_MSG_REF WHERE document_id in (SELECT document_id FROM temp_document_dlv_msg_ref t);
debug_purge('DOCUMENT_DLV_MSG_REF');
COMMIT ;
v_stmt := 'Purge DOCUMENT_CI_REF' ;
DELETE FROM DOCUMENT_CI_REF WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('DOCUMENT_CI_REF');
COMMIT ;
v_stmt := 'Purge DLV_MESSAGE' ;
DELETE FROM DLV_MESSAGE where message_guid in ( SELECT message_guid from temp_document_dlv_msg_ref );
debug_purge('DLV_MESSAGE');
COMMIT ;
v_stmt := 'Purge DLV_SUBSCRIPTION';
DELETE FROM DLV_SUBSCRIPTION s WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('DLV_SUBSCRIPTION');
COMMIT ;
v_stmt := 'Purge CUBE_INSTANCE';
DELETE FROM cube_instance ci WHERE cikey in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('CUBE_INSTANCE');
COMMIT ;
-- ============ BPMN Tables ============
IF table_exist('BPM_AUDIT_QUERY') THEN
v_stmt := 'Purge BPM_AUDIT_QUERY' ;
DELETE FROM BPM_AUDIT_QUERY WHERE component_instance_id in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('BPM_AUDIT_QUERY');
COMMIT ;
END IF;
IF table_exist('BPM_MEASUREMENT_ACTIONS') THEN
v_stmt := 'Purge BPM_MEASUREMENT_ACTIONS';
DELETE FROM BPM_MEASUREMENT_ACTIONS WHERE component_instance_id in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('BPM_MEASUREMENT_ACTIONS');
COMMIT ;
END IF;
IF table_exist('BPM_MEASUREMENT_ACTION_EXCEPS') THEN
v_stmt := 'Purge BPM_MEASUREMENT_ACTION_EXCEPS';
DELETE FROM BPM_MEASUREMENT_ACTION_EXCEPS WHERE component_instance_id in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('BPM_MEASUREMENT_ACTION_EXCEPS');
COMMIT ;
END IF;
IF table_exist('BPM_CUBE_AUDITINSTANCE') THEN
v_stmt := 'Purge BPM_CUBE_AUDITINSTANCE';
DELETE FROM BPM_CUBE_AUDITINSTANCE WHERE componentinstanceid in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('BPM_CUBE_AUDITINSTANCE');
COMMIT ;
END IF;
IF table_exist('BPM_CUBE_TASKPERFORMANCE') THEN
v_stmt := 'Purge BPM_CUBE_TASKPERFORMANCE';
DELETE FROM BPM_CUBE_TASKPERFORMANCE WHERE componentinstanceid in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('BPM_CUBE_TASKPERFORMANCE');
COMMIT ;
END IF;
IF table_exist('BPM_CUBE_PROCESSPERFORMANCE') THEN
v_stmt := 'Purge BPM_CUBE_PROCESSPERFORMANCE';
DELETE FROM BPM_CUBE_PROCESSPERFORMANCE WHERE componentinstanceid in (SELECT tpic.cikey FROM temp_cube_instance tpic);
debug_purge('BPM_CUBE_PROCESSPERFORMANCE');
COMMIT ;
END IF;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END purge_bpel_tables;
/*
* Procedure : deleteComponentInstances
*
* Description:
* This procedure is provided a table with composite instance ids that are purge able.
* Following temp tables are created and passed to purge_bpel_tables procedure.
*
* 1. temp_cube_instance
* 2. temp_document_ci_ref
* 3. temp_document_dlv_msg_ref
*
*
* 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 temp_cube_instance(CIKEY) ' ||
'SELECT cikey FROM cube_instance ci, '||p_id_table || ' comp WHERE ci.ecid = comp.ECID';
EXECUTE immediate v_stmt;
debug_purge('temp_cube_instance', 'Inserted = ');
purge_bpel_tables();
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteComponentInstances;
--@@orabpel_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 cmpst_id is null
*
* HEADERS_PROPERTIES
* AG_INSTANCE
* TEST_DETAILS
* CUBE_SCOPE
* AUDIT_COUNTER
* AUDIT_TRAIL
* AUDIT_DETAILS
* CI_INDEXS
* WORK_ITEM
* WI_FAULT
* XML_DOCUMENT
* DOCUMENT_DLV_MSG_REF
* DOCUMENT_CI_REF
* DLV_MESSAGE
* DLV_SUBSCRIPTION
* CUBE_INSTANCE
*
*/
FUNCTION deleteNoCompositeIdInstances( p_min_creation_date in timestamp,
p_max_creation_date in timestamp,
p_older_than TIMESTAMP, p_rownum NUMBER,
soa_partition_name in varchar2,
composite_name in varchar2,
composite_revision in varchar2 )
RETURN BOOLEAN
AS
v_stmt VARCHAR2(2000);
v_count NUMBER;
rowsFound_flag boolean := false ;
v_cmpst_stmt varchar2(2000) := '';
BEGIN
truncate_temp_tables();
EXECUTE IMMEDIATE 'INSERT INTO temp_document_dlv_msg_ref (message_guid,document_id) '||
'SELECT m.message_guid,ddmr.document_id ' ||
'FROM document_dlv_msg_ref ddmr, dlv_message m ' ||
'WHERE ddmr.message_guid = m.message_guid ' ||
'AND m.state in (2,3) AND m.cikey = 0 and ROWNUM <= ' ||p_rownum ;
if SQL%ROWCOUNT != 0 then
rowsFound_flag :=true ;
end if;
debug_purge('temp_document_dlv_msg_ref', 'Inserted no cikey ');
v_stmt := 'Create temp_cube_instance using CPST_INST_CREATED_TIME' ;
if soa_partition_name is not null then
v_cmpst_stmt := ' AND domain_name = ''' || soa_partition_name || '''';
end if;
if composite_name is not null then
v_cmpst_stmt := v_cmpst_stmt || ' AND composite_name = ''' || composite_name ||'''';
end if;
if composite_name is not null and composite_revision is not null then
v_cmpst_stmt := v_cmpst_stmt || ' AND composite_revision = ''' || composite_revision ||'''' ;
end if;
EXECUTE IMMEDIATE 'INSERT INTO temp_cube_instance(CIKEY, ECID) ' ||
'SELECT CIKEY, ECID FROM CUBE_INSTANCE ci WHERE STATE >= 5 AND MODIFY_DATE < '||
''''|| p_older_than ||''''|| ' AND CPST_INST_CREATED_TIME BETWEEN '||'''' ||
p_min_creation_date ||''''|| ' AND ' ||''''|| p_max_creation_date ||''''||
' AND NOT EXISTS (SELECT 1 FROM DLV_MESSAGE dlv WHERE dlv.STATE IN (0,1) AND dlv.ECID=ci.ECID) ' ||
v_cmpst_stmt ||
' and CMPST_ID is null and ROWNUM <= ' || p_rownum ;
if SQL%ROWCOUNT = 0 and rowsFound_flag = false then
log_info('No BPEL instances found with null composite instance ids');
RETURN FALSE;
end if;
debug_purge('temp_cube_instance', 'Inserted = ');
-- Remove cikeys that :whave a recoverable invoke/callback
-- These are recoverable dlv message rows that are in min and max date
--v_stmt := 'Create temp_document_dlv_msg_ref from dlv_message type = 1 and 2 that are in state 0';
--EXECUTE IMMEDIATE 'INSERT INTO temp_document_dlv_msg_ref (message_guid, document_id) SELECT m.message_guid, ddmr.document_id ' ||
--' FROM dlv_message m, document_dlv_msg_ref ddmr WHERE m.cikey = 0 and m.message_guid=ddmr.message_guid and RECEIVE_DATE BETWEEN '||'''' || p_min_creation_date ||''''|| ' AND ' ||''''|| p_max_creation_date ||''''|| ' AND ROWNUM <= ' || p_rownum ;
--debug_purge('temp_document_dlv_msg_ref', 'Inserted = ');
-- do not delete the recoverable invoke/callbacks
purge_bpel_tables();
return TRUE;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END deleteNoCompositeIdInstances ;
--@@orabpel_deleteComponentInstancesDOP.sql
/*
*
* deleteComponentInstancesDOP.sql
*
* Copyright (c) 2010, 2012, Oracle and/or its affiliates. All rights reserved.
*
* NAME
* deleteComponentInstancesDOP.sql - BPEL purge script
*
*
* DESCRIPTION
* This script is used to purge BPEL 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 BPEL 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: BPEL retention period for purge able data
*
*/
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_name in varchar2,
composite_revision in varchar2,
soa_partition_name in varchar2
)
AS
v_stmt VARCHAR2(2000);
v_rownum INTEGER;
v_cmpst_stmt varchar2(2000) := '';
BEGIN
truncate_temp_tables() ;
-- Add all ciKeys that have a composite instance id
-- p_idTable has the pruned list, so add all to the temp table
v_stmt := 'INSERT INTO temp_cube_instance ' ||
'SELECT cikey, ci.ecid FROM cube_instance ci, '||p_id_table || '
comp WHERE ci.ecid = comp.ecid';
EXECUTE immediate v_stmt;
debug_purge('temp_cube_instance', 'Inserted =');
-- if we still have space, all the no composite instance id rows too
-- All other BPEL temp tables are created based off temp_cube_instance
v_rownum := p_max_count - SQL%ROWCOUNT ;
IF v_rownum > 0 THEN
if soa_partition_name is not null then
v_cmpst_stmt := ' AND domain_name = ''' || soa_partition_name || '''';
end if;
if composite_name is not null then
v_cmpst_stmt := v_cmpst_stmt || ' AND composite_name = ''' || composite_name ||'''';
end if;
if composite_name is not null and composite_revision is not null then
v_cmpst_stmt := v_cmpst_stmt || ' AND composite_revision = ''' || composite_revision ||'''' ;
end if;
EXECUTE IMMEDIATE 'INSERT INTO temp_cube_instance(CIKEY, ECID) ' ||
'SELECT CIKEY, ECID FROM CUBE_INSTANCE ci WHERE STATE >= 5 AND MODIFY_DATE < '||
''''|| p_older_than ||''''|| ' AND CPST_INST_CREATED_TIME BETWEEN '||'''' ||
p_min_creation_date ||''''|| ' AND ' ||''''|| p_max_creation_date ||''''||
v_cmpst_stmt ||
' AND NOT EXISTS (SELECT 1 FROM DLV_MESSAGE dlv WHERE dlv.STATE IN (0,1) AND dlv.ECID=ci.ECID) ' ||
' and CMPST_ID is null and ROWNUM <= ' || v_rownum ;
debug_purge('temp_cube_instance', 'Inserted = ');
END IF ;
v_stmt := 'Create temp_document_ci_ref' ;
INSERT INTO temp_document_ci_ref SELECT document_id,t.cikey, t.ecid
FROM document_ci_ref d, temp_cube_instance t WHERE d.cikey = t.cikey;
debug_purge('temp_document_ci_ref', 'Inserted = ');
v_stmt := 'Create temp_document_dlv_msg_ref from dlv_message type = 1 or 2';
INSERT INTO temp_document_dlv_msg_ref SELECT m.message_guid, ddmr.document_id,t.cikey,t.ecid
FROM document_dlv_msg_ref ddmr, dlv_message m , temp_cube_instance t
WHERE ddmr.message_guid = m.message_guid
AND m.state in (2,3) AND ( m.cikey = t.cikey OR m.cikey = 0 );
debug_purge('temp_document_dlv_msg_ref', 'Inserted = ');
--v_rownum := p_max_count - SQL%ROWCOUNT ;
--IF v_rownum > 0 THEN
-- These are recoverable dlv message rows that are in min and max date
--v_stmt := 'Create temp_document_dlv_msg_ref from dlv_message type = 1 or 2 that are in state 0';
--EXECUTE IMMEDIATE 'INSERT INTO temp_document_dlv_msg_ref (message_guid, document_id,cikey) SELECT m.message_guid, ddmr.document_id,m.cikey ' ||
--' FROM document_dlv_msg_ref ddmr, dlv_message m WHERE m.cikey = 0 and m.message_guid=ddmr.message_guid and RECEIVE_DATE BETWEEN '||'''' || p_min_creation_date ||''''|| ' AND ' ||''''|| p_max_creation_date ||''''|| ' AND ROWNUM <= ' || v_rownum ;
--debug_purge('temp_document_dlv_msg_ref', 'Inserted = ');
--END IF;
v_stmt := 'create temp_xml_document';
INSERT INTO temp_xml_document SELECT tpic.document_id,tpic.cikey,tpic.ecid FROM temp_document_dlv_msg_ref tpic;
COMMIT;
INSERT INTO temp_xml_document select tpic.document_id,tpic.cikey,tpic.ecid FROM temp_document_ci_ref tpic;
COMMIT;
DELETE FROM temp_xml_document txd1 WHERE txd1.rowid > ANY (SELECT txd2.rowid FROM temp_xml_document txd2 WHERE txd1.document_id = txd2.document_id);
COMMIT;
EXCEPTION
when others then
log_error(v_stmt);
raise;
END createTempTables;
/*
* Procedure: deleteComponentInstaces
*
* Description
* This procedure purges following BPEL tables based on temp tables created in earlier step.
*
* HEADERS_PROPERTIES
* AG_INSTANCE
* TEST_DETAILS
* CUBE_SCOPE
* AUDIT_COUNTER
* AUDIT_TRAIL
* AUDIT_DETAILS
* CI_INDEXS
* WORK_ITEM
* WI_FAULT
* XML_DOCUMENT
* DOCUMENT_DLV_MSG_REF
* DOCUMENT_CI_REF
* DLV_MESSAGE
* DLV_SUBSCRIPTION
* CUBE_INSTANCE
*
* BPM_AUDIT_QUERY
* BPM_MEASUREMENT_ACTIONS
* BPM_MEASUREMENT_ACTION_EXCEPS
* BPM_CUBE_AUDITINSTANCE
* BPM_CUBE_TASKPERFORMANCE
* BPM_CUBE_PROCESSPERFORMANCE
*
*
* 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;
f1_flag boolean:=true;
f2_flag boolean:=true;
f3_flag boolean:=true;
f4_flag boolean:=true;
cursor_flag boolean:=true;
v_stoptime date :=p_stop_time;
-- Added for cursor optimization
l_num_rows number := 0;
TYPE purge_num_type IS TABLE OF NUMBER;
l_cube_instances purge_num_type;
CURSOR c_temp_cube_instance_thread IS
SELECT tpic.cikey FROM temp_cube_instance tpic
WHERE tpic.ecid IS NULL AND mod (tpic.cikey, p_dop)=p_thread
OR (tpic.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(tpic.ecid,0,p_dop), p_dop)=p_thread);
BEGIN
OPEN c_temp_cube_instance_thread;
v_deleted := true;
while v_deleted LOOP -- this loop is for keeping transactions short
v_deleted := false;
log_info('Begin BPEL purge loop ' || v_counter || ' for thread = ' || p_thread,write_file);
FETCH c_temp_cube_instance_thread BULK COLLECT into l_cube_instances LIMIT p_batch_size;
l_num_rows := l_cube_instances.count;
log_info('cube instances count = ' || l_num_rows, write_file);
v_stmt := 'Purge HEADERS_PROPERTIES' ;
IF f1_flag then
f1_flag:=false;
DELETE FROM HEADERS_PROPERTIES WHERE message_guid IN (SELECT tddmr.message_guid
FROM temp_document_dlv_msg_ref tddmr WHERE
mod (dbms_utility.get_hash_value(tddmr.message_guid,0,p_dop), p_dop)=p_thread
)
AND rownum <= p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f1_flag:=true;
debug_purge('HEADERS_PROPERTIES',null,write_file);
COMMIT;
END IF;
END IF;
v_stmt := 'Purge AG_INSTANCE' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM AG_INSTANCE WHERE cikey = l_cube_instances(i);
debug_purge('AG_INSTANCE',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge TEST_DETAILS' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM TEST_DETAILS WHERE cikey = l_cube_instances(i);
debug_purge('TEST_DETAILS',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge CUBE_SCOPE' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM CUBE_SCOPE WHERE cikey = l_cube_instances(i);
debug_purge('CUBE_SCOPE',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge AUDIT_COUNTER' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM AUDIT_COUNTER WHERE cikey = l_cube_instances(i);
debug_purge('AUDIT_COUNTER',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge AUDIT_TRAIL' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM AUDIT_TRAIL WHERE cikey = l_cube_instances(i);
debug_purge('AUDIT_TRAIL',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge AUDIT_DETAILS' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM AUDIT_DETAILS WHERE cikey = l_cube_instances(i);
debug_purge('AUDIT_DETAILS',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge CI_INDEXS' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM CI_INDEXES WHERE cikey = l_cube_instances(i);
debug_purge('CI_INDEXES',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge WORK_ITEM' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM WORK_ITEM WHERE cikey = l_cube_instances(i);
debug_purge('WORK_ITEM',null,write_file);
COMMIT ;
END IF;
v_stmt := 'Purge WI_FAULT' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM WI_FAULT WHERE cikey = l_cube_instances(i);
debug_purge('WI_FAULT',null,write_file);
COMMIT ;
END IF;
v_stmt := 'Purge XML_DOCUMENT';
IF f2_flag then
f2_flag:=false;
DELETE FROM XML_DOCUMENT d WHERE d.document_id IN (
SELECT tpic.document_id FROM temp_xml_document tpic WHERE
(tpic.ecid IS NULL AND mod (tpic.cikey, p_dop)=p_thread)
OR (tpic.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(tpic.ecid,0,p_dop), p_dop)=p_thread))
AND rownum < p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f2_flag := true;
debug_purge('XML_DOCUMENT',null,write_file);
COMMIT ;
END IF;
END IF;
v_stmt := 'Purge DOCUMENT_DLV_MSG_REF' ;
IF f3_flag then
f3_flag:=false;
DELETE FROM DOCUMENT_DLV_MSG_REF d WHERE d.document_id IN (
SELECT tpic.document_id FROM temp_document_dlv_msg_ref tpic WHERE
(tpic.ecid IS NULL AND mod (tpic.cikey, p_dop)=p_thread)
OR (tpic.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(tpic.ecid,0,p_dop), p_dop)=p_thread))
AND rownum <= p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f3_flag := true;
debug_purge('DOCUMENT_DLV_MSG_REF',null,write_file);
COMMIT ;
END IF;
END IF;
v_stmt := 'Purge DOCUMENT_CI_REF' ;
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM DOCUMENT_CI_REF WHERE cikey = l_cube_instances(i);
debug_purge('DOCUMENT_CI_REF',null,write_file);
COMMIT ;
END IF;
v_stmt := 'Purge DLV_MESSAGE' ;
IF f4_flag then
f4_flag:=false;
DELETE FROM DLV_MESSAGE m where message_guid in (
select tddmr.message_guid from temp_document_dlv_msg_ref tddmr where
mod (dbms_utility.get_hash_value(tddmr.message_guid,0,p_dop), p_dop)=p_thread
)
AND rownum <= p_batch_size;
IF SQL%FOUND THEN
v_deleted := true;
f4_flag := true;
debug_purge('DLV_MESSAGE',null,write_file);
COMMIT ;
END IF;
END IF;
v_stmt := 'Purge DLV_SUBSCRIPTION';
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM DLV_SUBSCRIPTION WHERE cikey = l_cube_instances(i);
debug_purge('DLV_SUBSCRIPTION',null,write_file);
COMMIT;
END IF;
v_stmt := 'Purge CUBE_INSTANCE';
IF cursor_flag then
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM CUBE_INSTANCE WHERE cikey = l_cube_instances(i);
debug_purge('CUBE_INSTANCE',null,write_file);
commit;
END IF;
-- ============= BPMN Tables =====================
v_stmt := 'Purge BPM_AUDIT_QUERY' ;
IF cursor_flag THEN
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM BPM_AUDIT_QUERY WHERE component_instance_id = to_char(l_cube_instances(i));
debug_purge('BPM_AUDIT_QUERY', null, write_file);
COMMIT;
END IF;
v_stmt := 'Purge BPM_MEASUREMENT_ACTIONS' ;
IF cursor_flag THEN
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM BPM_MEASUREMENT_ACTIONS WHERE component_instance_id = to_char(l_cube_instances(i));
debug_purge('BPM_MEASUREMENT_ACTIONS', null, write_file);
COMMIT;
END IF;
v_stmt := 'Purge BPM_MEASUREMENT_ACTION_EXCEPS' ;
IF cursor_flag THEN
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM BPM_MEASUREMENT_ACTION_EXCEPS WHERE component_instance_id = to_char(l_cube_instances(i));
debug_purge('BPM_MEASUREMENT_ACTION_EXCEPS', null, write_file);
COMMIT;
END IF;
v_stmt := 'Purge BPM_CUBE_AUDITINSTANCE' ;
IF cursor_flag THEN
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM BPM_CUBE_AUDITINSTANCE WHERE componentinstanceid = to_char(l_cube_instances(i));
debug_purge('BPM_CUBE_AUDITINSTANCE', null, write_file);
COMMIT;
END IF;
v_stmt := 'Purge BPM_CUBE_TASKPERFORMANCE' ;
IF cursor_flag THEN
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM BPM_CUBE_TASKPERFORMANCE WHERE componentinstanceid = to_char(l_cube_instances(i));
debug_purge('BPM_CUBE_TASKPERFORMANCE', null, write_file);
COMMIT;
END IF;
v_stmt := 'Purge BPM_CUBE_PROCESSPERFORMANCE' ;
IF cursor_flag THEN
FORALL i in l_cube_instances.FIRST..l_cube_instances.LAST
DELETE FROM BPM_CUBE_PROCESSPERFORMANCE WHERE componentinstanceid = to_char(l_cube_instances(i));
debug_purge('BPM_CUBE_PROCESSPERFORMANCE', null, write_file);
COMMIT;
END IF;
cursor_flag := false;
if l_num_rows = p_batch_size then
cursor_flag := true;
v_deleted := true;
end if;
v_counter := v_counter + 1;
-- exit loop if out of ime
IF (sysdate >= v_stoptime) THEN
v_deleted := FALSE;
CLOSE c_temp_cube_instance_thread;
return FALSE ;
END IF;
END LOOP;
CLOSE c_temp_cube_instance_thread;
return TRUE;
EXCEPTION
when others then
log_error(v_stmt, write_file);
CLOSE c_temp_cube_instance_thread;
raise;
END deleteComponentInstances;
--@@orabpel_isComponentPartitioned.sql
function isComponentPartitioned return boolean is
begin
return is_table_partitioned('CUBE_INSTANCE','CPST_INST_CREATED_TIME');
end isComponentPartitioned;
END soa_orabpel;
/
--show errors;
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--@@fabric/purge_fabric_oracle.sql
Rem
Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem purge_fabric_oracle.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Fabric tables
Rem
Rem
BEGIN
IF table_exist('COMPOSITE_INSTANCE_PURGE') THEN
EXECUTE IMMEDIATE 'DROP TABLE COMPOSITE_INSTANCE_PURGE';
END IF;
IF table_exist('REFERENCE_INSTANCE_PURGE') THEN
EXECUTE IMMEDIATE 'DROP TABLE REFERENCE_INSTANCE_PURGE';
END IF;
IF table_exist('COMPOSITE_FAULT_PURGE') THEN
EXECUTE IMMEDIATE 'DROP TABLE COMPOSITE_FAULT_PURGE';
END IF;
IF table_exist('REJECTED_MESSAGE_PURGE') THEN
EXECUTE IMMEDIATE 'DROP TABLE REJECTED_MESSAGE_PURGE';
END IF;
IF table_exist('COMPONENT_INSTANCE_PURGE') THEN
EXECUTE IMMEDIATE 'DROP TABLE COMPONENT_INSTANCE_PURGE';
END IF;
EXECUTE IMMEDIATE 'CREATE TABLE composite_instance_purge (id integer,ecid varchar2(100))';
EXECUTE IMMEDIATE 'CREATE TABLE reference_instance_purge (id integer,ecid varchar2(100))';
EXECUTE IMMEDIATE 'CREATE TABLE composite_fault_purge (id integer, ecid varchar2(100))';
EXECUTE IMMEDIATE 'CREATE TABLE rejected_message_purge (id integer,ecid varchar2(100))';
EXECUTE IMMEDIATE 'CREATE TABLE component_instance_purge (id varchar2(100),ecid varchar2(100))';
END;
/
CREATE OR REPLACE PACKAGE soa_fabric as
PROCEDURE deleteCompositeInstances(instance_id_table in varchar2 );
FUNCTION deleteNoCompositeIdInstances(min_created_date in timestamp,
max_created_date in timestamp,
retention_period in timestamp,
batch_size in integer,
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);
PROCEDURE delete_composite_inst_parallel(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time date default NULL,
write_file in utl_file.file_type default NULL) ;
function isComponentPartitioned return boolean;
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);
end soa_fabric;
/
CREATE OR REPLACE PACKAGE BODY soa_fabric AS
--@@fabric_delete_composite_instances.sql
/*
Rem
Rem Copyright (c) 2006, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem delete_composite_instances.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Fabric purge
Rem
*/
procedure delete_instance_payloads(id_table in varchar2,instance_type in varchar2) as
delete_sql varchar2(1000);
begin
delete_sql := 'delete from xml_document where document_id in (
select payload_key from instance_payload where instance_id in ( select id from ' || id_table ||') and
instance_type = '''|| instance_type ||''') ';
execute immediate delete_sql;
debug_purge('xml_document');
commit;
if instance_type = 'service' then
delete_sql := 'delete from rejected_msg_native_payload where id in (
select payload_key from instance_payload where instance_id in (select id from ' || id_table ||') and
instance_type = '''|| instance_type ||''')';
execute immediate delete_sql;
debug_purge('rejected_msg_native_payload');
commit;
end if;
delete_sql := 'delete from instance_payload where instance_id in (select id from ' || id_table ||') and instance_type = '''|| instance_type ||'''';
execute immediate delete_sql;
debug_purge('instance_payload');
commit;
end delete_instance_payloads;
function delete_compositeinstancefaults(composite_instance_id_table in varchar2) return integer is
id_sql varchar2(4000) := '';
add_and boolean := false;
purge_id_table varchar2(100) := 'composite_fault_purge';
delete_sql varchar2(1000);
total_rows integer;
begin
id_sql := ' select id from composite_instance_fault where composite_instance_id in (select id from composite_instance_purge)';
id_sql := 'insert into '||purge_id_table ||'(id)' || id_sql;
execute immediate 'truncate table ' || purge_id_table;
execute immediate id_sql;
total_rows := SQL%ROWCOUNT;
delete_instance_payloads(purge_id_table, 'service');
delete_sql := 'delete from composite_instance_fault where id in ( select id from ' || purge_id_table ||')';
execute immediate delete_sql;
debug_purge('composite_instance_fault');
commit;
execute immediate 'truncate table ' || purge_id_table;
return total_rows;
exception
when others then
log_error('ERROR (delete_composite_faults)');
raise;
end delete_compositeinstancefaults;
function delete_reference_instances(composite_instance_id_table in varchar2) return integer is
id_sql varchar2(4000) := '';
add_and boolean := false;
purge_id_table varchar2(100) :='reference_instance_purge';
delete_sql varchar2(1000);
total_rows integer;
begin
id_sql := ' select id from reference_instance where composite_instance_id in (select id from composite_instance_purge)';
id_sql := 'insert into '||purge_id_table ||'(id) ' || id_sql;
execute immediate 'truncate table ' || purge_id_table;
execute immediate id_sql;
total_rows := SQL%ROWCOUNT;
debug_purge(purge_id_table,'inserted = ');
commit;
delete_instance_payloads(purge_id_table, 'reference');
delete_sql := 'delete from reference_instance where id in ( select id from ' || purge_id_table ||')';
execute immediate delete_sql;
debug_purge('reference_instance');
commit;
execute immediate 'truncate table ' ||purge_id_table;
return total_rows;
exception
when others then
log_error('ERROR (delete_reference_instances)');
raise;
end delete_reference_instances;
procedure deleteCompositeInstances(instance_id_table in varchar2) is
v_sql varchar2(1000);
total_rows integer;
purge_id_table varchar2(100) :='composite_instance_purge';
begin
v_sql :='insert into ' || purge_id_table || '(id) select id from composite_instance where ecid in (select ecid from '||instance_id_table||')';
execute immediate 'truncate table ' ||purge_id_table;
execute immediate v_sql;
total_rows := delete_reference_instances(purge_id_table);
total_rows := delete_compositeinstancefaults(purge_id_table);
delete_instance_payloads(purge_id_table, 'composite');
v_sql := 'delete from composite_sensor_value where composite_instance_id in ( select id from ' || purge_id_table ||')';
execute immediate v_sql;
debug_purge('composite_sensor_value');
commit;
v_sql := 'delete from composite_instance_assoc where assoc1_composite_instance_id in ( select id from ' || purge_id_table ||') or assoc2_composite_instance_id in ( select id from ' || purge_id_table ||')';
execute immediate v_sql;
debug_purge('composite_instance_assoc');
commit;
v_sql := 'delete from component_instance where composite_instance_id in (select id from '|| purge_id_table||')';
execute immediate v_sql;
debug_purge('component_instance');
commit;
v_sql := 'delete from attachment where key in (select key from attachment_ref where ecid in (select ecid from '||instance_id_table||'))';
execute immediate v_sql;
debug_purge('attachment');
commit;
v_sql := 'delete from attachment_ref where ecid in (select ecid from '||instance_id_table ||')';
execute immediate v_sql;
debug_purge('attachment_ref');
commit;
v_sql := 'delete from composite_instance where id in ( select id from ' || purge_id_table ||')';
execute immediate v_sql;
debug_purge('composite_instance');
commit;
exception
when others then
log_error('ERROR (fabric.deleteCompositeInstances)');
raise;
end deleteCompositeInstances;
--@@fabric_delete_composite_inst_parallel.sql
/*
Rem Copyright (c) 2006, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem delete_composite_inst_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
*/
procedure delete_inst_pylds_parallel(id_table in varchar2,instance_type in varchar2, p_dop in number,p_thread in number,p_batch_size in number,write_file in utl_file.file_type) as
delete_sql varchar2(4000);
v_deleted boolean :=true;
begin
while v_deleted LOOP
v_deleted :=false;
delete_sql := 'delete from xml_document where document_id in ( select payload_key from instance_payload where instance_id in ( select id from '||id_table||' ip WHERE (ip.ecid IS NULL AND mod (ip.id, '||p_dop||')=' || p_thread ||') OR (ip.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(ip.ecid,0,'||p_dop||'), '||p_dop||')='||p_thread||')) and instance_type = '''|| instance_type ||''' and rownum <=' ||p_batch_size||' )' ;
execute immediate delete_sql;
if SQL%FOUND then
v_deleted :=true;
end if;
debug_purge('xml_document',null,write_file);
if instance_type = 'service' then
delete_sql := 'delete from rejected_msg_native_payload where id in (
select payload_key from instance_payload where instance_id in ( select id from '||id_table||' ip WHERE
(ip.ecid IS NULL AND mod (ip.id,'|| p_dop||')=' || p_thread ||')
OR (ip.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(ip.ecid,0,'||p_dop||'),'|| p_dop||')='||p_thread||')) and instance_type = '''|| instance_type ||''' and rownum<='||p_batch_size||')';
execute immediate delete_sql;
if SQL%FOUND then
v_deleted :=true;
end if;
debug_purge('rejected_msg_native_payload',null,write_file);
end if;
delete_sql := 'delete from instance_payload where instance_id in (
select id from '||id_table||' ip WHERE
(ip.ecid IS NULL AND mod (ip.id, '||p_dop||')=' || p_thread ||')
OR (ip.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(ip.ecid,0,'||p_dop||'),'|| p_dop||')='||p_thread||')) and instance_type = '''|| instance_type ||''''||' and rownum<='||p_batch_size ;
execute immediate delete_sql;
if SQL%FOUND then
v_deleted :=true;
end if;
debug_purge('instance_payload',null,write_file);
commit;
END LOOP;
EXCEPTION
when others then
log_error('ERROR(delete_inst_pylds_parallel)',write_file);
raise;
end delete_inst_pylds_parallel;
procedure delete_ref_inst_parallel(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file in utl_file.file_type ) is
purge_id_table varchar2(100) :='reference_instance_purge';
delete_sql varchar2(4000);
v_deleted boolean := true;
begin
delete_inst_pylds_parallel(purge_id_table, 'reference',p_dop,p_thread,p_batch_size,write_file);
while v_deleted LOOP
v_deleted :=false;
delete_sql:='delete from reference_instance where id in ( select id from reference_instance_purge rip WHERE
(rip.ecid IS NULL AND mod (rip.id,'|| p_dop||')='||p_thread||')
OR (rip.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(rip.ecid,0,'||p_dop||'),'|| p_dop||')='||p_thread||'))AND rownum <= '|| p_batch_size;
execute immediate delete_sql;
IF SQL%FOUND THEN
v_deleted := true;
END IF;
debug_purge('reference_instance',null,write_file);
commit;
log_info('time check',write_file);
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'),write_file);
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE ;
END IF;
END LOOP;
exception
when others then
log_error('ERROR(delete_ref_inst_parallel)',write_file);
raise;
end delete_ref_inst_parallel;
procedure delete_cmp_fault_parallel(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file in utl_file.file_type ) is
purge_id_table varchar2(100) :='composite_fault_purge';
delete_sql varchar2(4000);
v_deleted boolean := true;
begin
delete_inst_pylds_parallel(purge_id_table, 'service',p_dop,p_thread,p_batch_size,write_file);
while v_deleted LOOP
v_deleted :=false;
delete_sql:='delete from composite_instance_fault where id in ( select id from composite_fault_purge rip WHERE
(rip.ecid IS NULL AND mod (rip.id,'|| p_dop||')='||p_thread||')
OR (rip.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(rip.ecid,0,'||p_dop||'),'|| p_dop||')='||p_thread||'))AND rownum <= '|| p_batch_size;
execute immediate delete_sql;
IF SQL%FOUND THEN
v_deleted := true;
END IF;
debug_purge('composite_instance_fault',null,write_file);
commit;
log_info('time check',write_file);
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'),write_file);
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE ;
END IF;
END LOOP;
exception
when others then
log_error('ERROR(delete_cmp_fault_parallel)',write_file);
raise;
end delete_cmp_fault_parallel;
procedure delete_component_instances(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file in utl_file.file_type) is
v_deleted boolean :=true;
delete_sql varchar2(4000);
begin
while v_deleted LOOP
v_deleted := false;
delete_sql:='delete from component_instance where id in ( select id from component_instance_purge cip WHERE
(cip.ecid IS NULL AND mod (cip.id,'|| p_dop||')='||p_thread||')
OR (cip.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(cip.ecid,0,'||p_dop||'),'|| p_dop||')='||p_thread||'))AND rownum <= '|| p_batch_size;
execute immediate delete_sql;
IF SQL%FOUND THEN
v_deleted := true;
END IF;
debug_purge('component_instance',null,write_file);
commit;
log_info('time check',write_file);
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'),write_file);
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE ;
END IF;
END LOOP;
exception
when others then
log_error('ERROR(delete_component_instances)',write_file);
raise;
end delete_component_instances ;
procedure delete_misc(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file in utl_file.file_type ) is
purge_id_table varchar2(100) :='composite_instance_purge';
v_deleted boolean := true;
begin
delete_inst_pylds_parallel(purge_id_table, 'composite',p_dop,p_thread,p_batch_size,write_file);
while v_deleted LOOP
v_deleted :=false;
delete from composite_sensor_value where composite_instance_id in ( select id from composite_instance_purge cip where mod (dbms_utility.get_hash_value(cip.ecid,0,p_dop), p_dop)=p_thread) AND rownum <= p_batch_size ;
IF SQL%FOUND THEN
v_deleted := true;
END IF;
debug_purge('composite_sensor_value',null,write_file);
commit;
delete from composite_instance_assoc where assoc1_composite_instance_id in ( select id from composite_instance_purge cip where mod (dbms_utility.get_hash_value(cip.ecid,0,p_dop), p_dop)=p_thread) AND rownum <= p_batch_size ;
IF SQL%FOUND THEN
v_deleted := true;
END IF;
debug_purge('composite_instance_assoc',null,write_file);
commit;
log_info('time check',write_file);
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'),write_file);
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE ;
END IF;
END LOOP;
exception
when others then
log_error('ERROR(delete_misc)',write_file);
raise;
end delete_misc;
procedure delete_attachments(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file in utl_file.file_type ) is
v_deleted boolean := true;
begin
while v_deleted LOOP
v_deleted :=false;
delete from attachment where key in (select key from attachment_ref where ecid in (
select ecid from composite_instance_purge cip where mod (dbms_utility.get_hash_value(cip.ecid,0,p_dop), p_dop)=p_thread) AND rownum <= p_batch_size);
debug_purge('attachment',null,write_file);
commit;
delete from attachment_ref where ecid in (select ecid from composite_instance_purge cip where mod (dbms_utility.get_hash_value(cip.ecid,0,p_dop), p_dop)=p_thread) AND
rownum <= p_batch_size;
debug_purge('attachment_ref',null,write_file);
if SQL%FOUND then
v_deleted :=true;
end if;
commit;
log_info('time check',write_file);
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'),write_file);
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE ;
END IF;
END LOOP;
exception
when others then
log_error('ERROR(delete_attachment)',write_file);
raise;
end delete_attachments;
procedure delete_rejected_messages(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time DATE default NULL,write_file in utl_file.file_type ) is
purge_id_table varchar2(100) :='rejected_message_purge';
delete_sql varchar2(4000);
v_deleted boolean := true;
begin
delete_inst_pylds_parallel(purge_id_table, 'service',p_dop,p_thread,p_batch_size,write_file);
while v_deleted LOOP
v_deleted :=false;
delete_sql:='delete from rejected_message where id in ( select id from rejected_message_purge rip WHERE
(rip.ecid IS NULL AND mod (rip.id,'|| p_dop||')='||p_thread||')
OR (rip.ecid IS NOT NULL and mod (dbms_utility.get_hash_value(rip.ecid,0,'||p_dop||'),'|| p_dop||')='||p_thread||'))AND rownum <= '|| p_batch_size;
execute immediate delete_sql;
IF SQL%FOUND THEN
v_deleted := true;
END IF;
debug_purge('rejected_message',null,write_file);
commit;
log_info('time check',write_file);
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'),write_file);
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE ;
END IF;
END LOOP;
exception
when others then
log_error('ERROR(delete_rejected_messages)',write_file);
raise;
end delete_rejected_messages;
procedure delete_composite_inst_parallel(p_dop NUMBER, p_thread NUMBER,
p_batch_size NUMBER default 20000,
p_stop_time date default NULL,write_file in utl_file.file_type default NULL ) is
v_deleted boolean :=true;
total_rows integer;
begin
log_info('starting fabric.delete_composite_insst_parallel',write_file);
IF (sysdate < p_stop_time) THEN
delete_ref_inst_parallel(p_dop,p_thread,p_batch_size,p_stop_time,write_file);
END IF;
IF (sysdate < p_stop_time) THEN
delete_cmp_fault_parallel(p_dop,p_thread,p_batch_size,p_stop_time,write_file);
END IF;
IF (sysdate < p_stop_time) THEN
delete_misc(p_dop,p_thread,p_batch_size,p_stop_time,write_file);
END IF;
IF(sysdate < p_stop_time) THEN
delete_component_instances(p_dop,p_thread,p_batch_size,p_stop_time,write_file);
END IF;
IF(sysdate < p_stop_time) THEN
delete_rejected_messages(p_dop,p_thread,p_batch_size,p_stop_time,write_file);
end if;
IF(sysdate < p_stop_time) THEN
delete_attachments(p_dop,p_thread,p_batch_size,p_stop_time,write_file);
end if;
while v_deleted LOOP
delete from composite_instance where id in ( select id from composite_instance_purge cip where mod (dbms_utility.get_hash_value(cip.ecid,0,p_dop), p_dop)=p_thread) AND rownum <= p_batch_size ;
total_rows := SQL%ROWCOUNT;
debug_purge('composite_instance',null,write_file);
commit;
IF total_rows < p_batch_size THEN
v_deleted := FALSE ;
END IF ;
log_info('time check',write_file);
log_info('sysdate = ' ||TO_CHAR(sysdate,'DD/MON/YYYY:HH24/MI'),write_file);
log_info('p_stop_time = ' ||TO_CHAR( p_stop_time,'DD/MON/YYYY:HH24/MI'),write_file);
IF (sysdate >= p_stop_time) THEN
v_deleted := FALSE ;
END IF;
end loop;
log_info('complete fabric.delete_composite_inst_parallel',write_file );
exception
when others then
log_error('ERROR(delete_composite_inst_parallel)',write_file);
rollback;
raise;
end delete_composite_inst_parallel;
--@@fabric_deleteNoCompositeIdInstances.sql
/*
Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem deleteNoCompositeIdInstances.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Fabric 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
*/
function delete_orp_component_instances(min_created_date in timestamp,
max_created_date in timestamp,
retention_period in timestamp,
batch_size in integer,
composite_dn in varchar2
) return boolean is
id_sql varchar2(4000) := '';
total_rows integer;
v_cmpst_stmt varchar2(2000) := '';
begin
if composite_dn is not null then
v_cmpst_stmt := ' and component_dn like ''' || composite_dn || '%''';
end if;
id_sql := ' delete from component_instance where ';
id_sql := id_sql || 'created_time >= ' || '''' || min_created_date || '''';
id_sql := id_sql || ' and created_time <= ' || '''' || max_created_date || '''';
id_sql := id_sql || v_cmpst_stmt;
id_sql := id_sql || 'and composite_instance_id is null and rownum <=' || batch_size;
execute immediate id_sql;
total_rows := SQL%ROWCOUNT;
debug_purge('component_instance');
commit;
if total_rows = batch_size then
return true;
else
return false;
end if ;
exception
when others then
log_error('ERROR (delete_orph_component_instances)');
raise;
end delete_orp_component_instances;
function delete_orp_reference_instances(min_created_date in timestamp,
max_created_date in timestamp,
retention_period in timestamp,
batch_size in integer,
composite_dn in varchar2
) return boolean is
id_sql varchar2(4000) := '';
purge_id_table varchar2(100) := 'reference_instance_purge';
delete_sql varchar2(1000);
total_rows integer;
more_rows_to_delete boolean;
v_cmpst_stmt varchar2(2000) := '';
begin
if composite_dn is not null then
v_cmpst_stmt := ' and composite_dn like ''' || composite_dn || '%''';
end if;
id_sql := ' select id from reference_instance where ';
id_sql := id_sql || 'created_time >= ' || '''' || min_created_date || '''';
id_sql := id_sql || ' and created_time <= ' || '''' || max_created_date || '''';
id_sql := id_sql || v_cmpst_stmt;
id_sql := id_sql || 'and composite_instance_id is null and rownum <=' || batch_size;
id_sql := 'insert into '||purge_id_table || '(id)' || id_sql;
execute immediate 'truncate table ' || purge_id_table;
execute immediate id_sql;
total_rows := SQL%ROWCOUNT;
debug_purge(purge_id_table, 'inserted = ');
commit;
delete_instance_payloads(purge_id_table, 'reference');
delete_sql := 'delete from reference_instance where id in ( select id from ' || purge_id_table ||')';
execute immediate delete_sql;
debug_purge('reference_instance');
commit;
execute immediate 'truncate table ' || purge_id_table;
if total_rows = batch_size then
return true;
else
return false;
end if ;
exception
when others then
log_error('ERROR (delete_orph_reference_instances)');
raise;
end delete_orp_reference_instances;
function delete_orp_compinstfaults(min_created_date in timestamp,
max_created_date in timestamp,
retention_period in timestamp,
batch_size in integer,
composite_dn in varchar2
) return boolean is
id_sql varchar2(4000) := '';
purge_id_table varchar2(100) := 'composite_fault_purge';
delete_sql varchar2(1000);
total_rows integer;
v_cmpst_stmt varchar2(2000) := '';
begin
if composite_dn is not null then
v_cmpst_stmt := ' and composite_dn like ''' || composite_dn || '%''';
end if;
id_sql := ' select id from composite_instance_fault where ';
id_sql := id_sql || 'created_time >= ' || '''' || min_created_date || '''';
id_sql := id_sql || ' and created_time <= ' || '''' || max_created_date || '''';
id_sql := id_sql || v_cmpst_stmt;
id_sql := id_sql || ' and ( composite_instance_id is null or composite_instance_id=0) and rownum <=' || batch_size;
id_sql := 'insert into '||purge_id_table || '(id)'|| id_sql;
execute immediate 'truncate table ' || purge_id_table;
execute immediate id_sql;
total_rows := SQL%ROWCOUNT;
debug_purge(purge_id_table,'inserted = ');
commit;
delete_instance_payloads(purge_id_table, 'service');
delete_sql := 'delete from composite_instance_fault where id in ( select id from ' || purge_id_table ||')';
execute immediate delete_sql;
debug_purge('composite_instance_fault');
commit;
execute immediate 'truncate table ' || purge_id_table;
if total_rows = batch_size then
return true;
else
return false;
end if ;
exception
when others then
log_error('ERROR (delete_orphaned_compositeinstancefaults)');
raise;
end delete_orp_compinstfaults;
function deleteNoCompositeIdInstances(min_created_date in timestamp,
max_created_date in timestamp,
retention_period in timestamp,
batch_size in integer,
composite_dn in varchar2
)
return boolean is
more_rows_to_delete_all boolean := false;
more_rows_to_delete boolean :=false;
begin
more_rows_to_delete_all := delete_orp_reference_instances(min_created_date,
max_created_date,
retention_period,
batch_size,
composite_dn
);
more_rows_to_delete := delete_orp_compinstfaults(min_created_date,
max_created_date,
retention_period,
batch_size,
composite_dn
);
more_rows_to_delete := delete_orp_component_instances(min_created_date,
max_created_date,
retention_period,
batch_size,
composite_dn
);
if(more_rows_to_delete = true) then
more_rows_to_delete_all := true;
end if ;
return more_rows_to_delete_all;
exception
when others then
log_error('ERROR (fabric.deleteNoCompositeIdInstances)');
raise;
end deleteNoCompositeIdInstances ;
--@@fabric_createTempTable.sql
/*
Rem
Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem createTempTables.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem Fabric 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
*/
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
id_sql varchar2(4000) := '';
v_rownum number;
v_cmpst_stmt varchar2(2000) := '';
begin
id_sql := ' select id,ecid from composite_instance where ecid in (select ecid from ' || p_id_table || ')';
id_sql := 'insert into composite_instance_purge ' || id_sql;
execute immediate 'truncate table composite_instance_purge';
execute immediate id_sql;
debug_purge('composite_instance_purge',' inserted = ');
commit;
if composite_dn is not null then
v_cmpst_stmt := ' and composite_dn like ''' || composite_dn || '%''';
end if;
id_sql :='insert into rejected_message_purge select id,ecid from rejected_message where ';
id_sql := id_sql || ' created_time >= ' || '''' ||p_min_creation_date || '''';
id_sql := id_sql || ' and created_time <= ' || '''' || p_max_creation_date || '''';
id_sql := id_sql || v_cmpst_stmt;
id_sql := id_sql || ' and rownum <= ' || p_max_count;
execute immediate 'truncate table rejected_message_purge';
execute immediate id_sql;
debug_purge('rejected_message_purge','inserted = ');
commit;
id_sql := ' select id, ecid from reference_instance where ecid in (select ecid from ' || p_id_table|| ')';
id_sql := 'insert into reference_instance_purge ' || id_sql;
execute immediate 'truncate table reference_instance_purge';
execute immediate id_sql;
v_rownum := p_max_count - SQL%ROWCOUNT;
IF v_rownum > 0 THEN
EXECUTE IMMEDIATE 'INSERT INTO reference_instance_purge ' ||
'SELECT ID, ECID FROM REFERENCE_INSTANCE WHERE '||
' created_time BETWEEN '||''''|| p_min_creation_date||''''||
' AND '||''''||p_max_creation_date ||'''' ||
v_cmpst_stmt ||
' AND ROWNUM <=' || v_rownum||' and composite_instance_id is null ';
END IF ;
debug_purge('reference_instance_purge','inserted = ' );
commit;
id_sql := ' select id, ecid from composite_instance_fault where ecid in (select ecid from ' || p_id_table|| ')';
id_sql:='insert into composite_fault_purge ' || id_sql;
execute immediate 'truncate table composite_fault_purge';
execute immediate id_sql;
v_rownum := p_max_count - SQL%ROWCOUNT;
IF v_rownum > 0 THEN
EXECUTE IMMEDIATE 'INSERT INTO composite_fault_purge ' ||
'SELECT ID, ECID FROM COMPOSITE_INSTANCE_FAULT WHERE '||
' created_time 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)) ';
END IF;
debug_purge('composite_fault_purge',' inserted = ');
commit;
id_sql := ' select id, ecid from component_instance where ecid in (select ecid from ' || p_id_table|| ')';
id_sql:='insert into component_instance_purge ' || id_sql;
execute immediate 'truncate table component_instance_purge';
execute immediate id_sql;
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 component_instance_purge ' ||
'SELECT ID, ECID FROM COMPONENT_INSTANCE WHERE '||
' CPST_PARTITION_DATE BETWEEN '||''''|| p_min_creation_date||''''||
' AND '||''''||p_max_creation_date ||'''' ||
v_cmpst_stmt ||
' AND ROWNUM <=' || v_rownum||' and composite_instance_id is null ';
END IF;
debug_purge('component_instance_purge',' inserted = ');
commit;
end createTempTables;
--@@fabric_isComponentPartitioned.sql
function isComponentPartitioned return boolean is
begin
return is_table_partitioned('COMPOSITE_INSTANCE','PARTITION_DATE');
end isComponentPartitioned;
No comments:
Post a Comment