SOA Purge Scripts

To execute the purge scripts:

1. In SQL*Plus, connect to the database AS SYSDBA:
   

   CONNECT SYS AS SYSDBA
   

2. Execute the following SQL commands:
   

   GRANT EXECUTE ON DBMS_LOCK to USER;
   GRANT CREATE ANY JOB TO USER;
   

   where USER is the soainfra account to execute the scripts. These privileges are required to run the scripts.
   
3. Load the purge scripts by executing the main purge script in the MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge directory.
   For a parallel purge, the debug logs from the jobs spawned by a parallel purge are logged into files created in the directory named SOA_PURGE_DIR. This directory must be accessible to the Oracle database.
   
4. Create SOA_PURGE_DIR and grant write permissions to the soainfra user.
   

   mkdir -p /tmp/purgelog
   CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS 'SERVER_DIRECTORY'
   

   where SERVER_DIRECTORY is the name of the directory to create (for example, '/tmp/purgelog/'). Note the required single quotes around the directory path.
   
5. If you want to execute the scripts in debug mode, run common/debug_on.sql and set serverout to on in SQL*Plus. This step is optional.
   

   SET SERVEROUTPUT ON
   

   The logs from the spawned jobs are logged into the directory created in Step 4 (separate files per job). The rest of the logs are displayed on stdout (or the spool file, if configured).
   There are two options for purging:
   
   • Looped purge
   • Parallel purge
6. Execute the purge scripts as shown below. Examples are provided for both options.
   
   1. For looped purge:

      DECLARE
      
         MAX_CREATION_DATE timestamp;
         MIN_CREATION_DATE timestamp;
         batch_size integer;
         max_runtime integer;
         retention_period timestamp;
      
        BEGIN
      
         MIN_CREATION_DATE := to_timestamp('2010-01-01','YYYY-MM-DD');
         MAX_CREATION_DATE := to_timestamp('2010-01-31','YYYY-MM-DD');
          max_runtime := 60;
          retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
         batch_size := 10000;
           soa.delete_instances(
           min_creation_date => MIN_CREATION_DATE,
           max_creation_date => MAX_CREATION_DATE,
           batch_size => batch_size,
           max_runtime => max_runtime,
           retention_period => retention_period,
           purge_partitioned_component => false);
        END;
        /
      

   2. For parallel purge:

      DECLARE
      
         max_creation_date timestamp;
         min_creation_date timestamp;
         retention_period timestamp;
        BEGIN
      
         min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
         max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
         retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
      
          soa.delete_instances_in_parallel(
           min_creation_date => min_creation_date,
           max_creation_date => max_creation_date,
           batch_size => 10000,
           max_runtime => 60,
           retention_period => retention_period,
           DOP => 3,
           max_count => 1000000,
           purge_partitioned_component => false);
      
       END;