Rem
Rem $Header: purge_instances_oracle.sql 14-sep-2007.11:48:18 ramisra Exp $
Rem
Rem purge_instances_oracle.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem purge_instances_oracle.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem This file deletes all cube instaces and related data which were closed before given date time
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem ramisra 09/13/07 - File to purge Instances
Rem ramisra 09/13/07 - Created
Rem
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
CREATE OR REPLACE PROCEDURE purge_instances (p_older_than TIMESTAMP)
AS
CURSOR c_cube_instance
IS
SELECT cikey
FROM cube_instance
WHERE state >= 5 AND modify_date < p_older_than;
CURSOR c_invoke_message
IS
SELECT message_guid
FROM invoke_message
WHERE state > 1 AND receive_date < p_older_than;
CURSOR c_dlv_message
IS
SELECT message_guid
FROM dlv_message
WHERE state > 1 AND receive_date < p_older_than;
BEGIN
-- Delete all closed instances older than specified date
--
FOR r_cube_instance IN c_cube_instance
LOOP
DELETE FROM native_correlation
WHERE conversation_id IN (SELECT conv_id from dlv_subscription
WHERE cikey = r_cube_instance.cikey );
DELETE FROM cube_instance
WHERE cikey = r_cube_instance.cikey;
DELETE FROM cube_scope
WHERE cikey = r_cube_instance.cikey;
DELETE FROM work_item
WHERE cikey = r_cube_instance.cikey;
DELETE FROM wi_exception
WHERE cikey = r_cube_instance.cikey;
DELETE FROM scope_activation
WHERE cikey = r_cube_instance.cikey;
DELETE FROM dlv_subscription
WHERE cikey = r_cube_instance.cikey;
DELETE FROM audit_trail
WHERE cikey = r_cube_instance.cikey;
DELETE FROM audit_details
WHERE cikey = r_cube_instance.cikey;
DELETE FROM sync_trail
WHERE cikey = r_cube_instance.cikey;
DELETE FROM sync_store
WHERE cikey = r_cube_instance.cikey;
DELETE FROM xml_document
WHERE dockey IN ( SELECT doc_ref.dockey FROM document_ci_ref doc_ref
WHERE doc_ref.cikey = r_cube_instance.cikey);
DELETE FROM document_dlv_msg_ref
WHERE dockey IN ( SELECT doc_ref.dockey FROM document_ci_ref doc_ref
WHERE doc_ref.cikey = r_cube_instance.cikey);
DELETE FROM document_ci_ref
WHERE cikey = r_cube_instance.cikey;
DELETE FROM attachment
WHERE key IN ( SELECT attach_ref.key FROM attachment_ref attach_ref
WHERE attach_ref.cikey = r_cube_instance.cikey);
DELETE FROM attachment_ref
WHERE cikey = r_cube_instance.cikey;
DELETE FROM ci_indexes
WHERE cikey = r_cube_instance.cikey;
COMMIT;
END LOOP;
-- Purge all handled invoke_messages older than specified date
--
FOR r_invoke_message IN c_invoke_message
LOOP
DELETE FROM invoke_message
WHERE message_guid = r_invoke_message.message_guid;
DELETE FROM xml_document
WHERE dockey IN (SELECT dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref
WHERE dlv_ref.message_guid = r_invoke_message.message_guid);
DELETE FROM document_ci_ref
WHERE dockey IN (SELECT dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref
WHERE dlv_ref.message_guid = r_invoke_message.message_guid);
DELETE FROM document_dlv_msg_ref
WHERE message_guid = r_invoke_message.message_guid;
COMMIT;
END LOOP;
-- Purge all handled callback messages older than specified date
--
FOR r_dlv_message IN c_dlv_message
LOOP
DELETE FROM dlv_message
WHERE message_guid = r_dlv_message.message_guid;
DELETE FROM xml_document
WHERE dockey IN (SELECT dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref
WHERE dlv_ref.message_guid = r_dlv_message.message_guid);
DELETE FROM document_ci_ref
WHERE dockey IN (SELECT dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref
WHERE dlv_ref.message_guid = r_dlv_message.message_guid);
DELETE FROM document_dlv_msg_ref
WHERE message_guid = r_dlv_message.message_guid;
COMMIT;
END LOOP;
-- delete all unreferenced xml_documents rows from xml_document table
DELETE FROM xml_document
WHERE dockey NOT IN (SELECT dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref
UNION
SELECT doc_ref.dockey FROM document_ci_ref doc_ref );
-- IF conversation_id is not present in dlv_subscription, we can delete it from native_correlation
DELETE FROM native_correlation
WHERE conversation_id NOT IN (SELECT conv_id from dlv_subscription);
END purge_instances;
/
COMMIT;
/*-----------------------------------------------------------------------------------------------
SELECT STATEMENTS TO COUNT THE NUMBER OF RECORDS IN EACH OF THE MAIN DEHYDRATION TABLES
-----------------------------------------------------------------------------------------------*/
--select count(*) from cube_instance where modify_date <= to_timestamp('2006-05-11 00:00:00','YYYY-MM-DD HH24:MI:SS')
--select count(*) from invoke_message where receive_date <= to_timestamp('2006-05-11 00:00:00','YYYY-MM-DD HH24:MI:SS')
--select count(*) from dlv_message where receive_date <= to_timestamp('2006-05-11 00:00:00','YYYY-MM-DD HH24:MI:SS')
/*-----------------------------------------------------------------------------------------------
CALLING THE STORED PROCEDURE EXAMPLES
Example 1: Allows the user to input date and time (24 hour format)
Example 2: Allows the user to input a date only
-----------------------------------------------------------------------------------------------*/
--call purge_instances(to_timestamp('2006-05-10 00:00:00','YYYY-MM-DD HH24:MI:SS'))
--call purge_instances(to_timestamp('2007-09-14','YYYY-MM-DD'))
/
