Oracle bpel 释放实例占用数据空间 脚本

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'))
/