How to Purge SOA instances manually in 12c

1. Copy “soa_purge12” directory from SOA host “/xxxx/fmw/soa/common/sql/soainfra/sql/oracle/122130/” path to your local machine “PurgeDir” folder and zip it (say PurgeDir.zip).
2. Share this zip with DBA to execute below steps.
3. Start a new putty session (DB host).
4. Copy the zip to DB server and unzip @ “/u01/app/oracle/stage” location (example).
5. cd /u01/app/oracle/stage/PurgeDir/soa_purge12/

mkdir purge

chmod 777 purge

6. sqlplus PREFIX_SOAINFRA/PASSWORD

      SQL> @soa_purge_scripts.sql

7. Run below with sysdba role.

SQL> CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS ‘/u01/app/oracle/stage/PurgeDir/soa_purge12/purge/’;

 SQL> GRANT READ, WRITE ON DIRECTORY SOA_PURGE_DIR TO PREFIX_SOAINFRA;

 SQL> GRANT EXECUTE ON DBMS_LOCK TO PREFIX_SOAINFRA;

 SQL> GRANT CREATE JOB TO PREFIX_SOAINFRA;

 SQL> GRANT alter session TO PREFIX_SOAINFRA;

8. Run below with PREFIX_SOAINFRA user (to know purgeable instances details and compare after purge).

 ———————-

set serveroutput on;

———————-

DECLARE

MAX_CREATION_DATE TIMESTAMP;

MIN_CREATION_DATE TIMESTAMP;

batch_size INTEGER;

retention_period TIMESTAMP;

purgeable_instance INTEGER;

table_partitioned INTEGER;

BEGIN

MAX_CREATION_DATE := to_timestamp(‘2020-03-31′,’YYYY-MM-DD’);       << END date >>

MIN_CREATION_DATE := to_timestamp(‘2020-01-01′,’YYYY-MM-DD’);        << START date >>

retention_period := to_timestamp(‘2020-03-31′,’YYYY-MM-DD’);                  << Keep same as END date >>

batch_size := 100000;

 if retention_period < max_creation_date then

retention_period := max_creation_date;

end if;

 select count(table_name) into table_partitioned from user_tables where partitioned = ‘YES’ and table_name=’SCA_FLOW_INSTANCE’;

 if table_partitioned > 0 then

DBMS_OUTPUT.PUT_LINE (‘SCA_FLOW_INSTANCE is partitioned ‘);

else

DBMS_OUTPUT.PUT_LINE (‘SCA_FLOW_INSTANCE is not partitioned ‘);

end if;

 SELECT Count(s.flow_id) into purgeable_instance

FROM sca_flow_instance s

WHERE s.created_time >= MIN_CREATION_DATE

AND s.created_time <= MAX_CREATION_DATE

AND s.updated_time <= retention_period

AND s.active_component_instances = 0

AND s.flow_id NOT IN (SELECT r.flow_id FROM temp_prune_running_insts r)

AND s.flow_id IN

(SELECT c.flow_id FROM sca_flow_to_cpst c, sca_entity e, sca_partition p WHERE c.composite_sca_entity_id = e.id)

AND rownum <= batch_size;

DBMS_OUTPUT.PUT_LINE (‘Total purgeable flow instance: ‘ || purgeable_instance);

END;

/

——————————————

9. Run below procedure with PREFIX_SOAINFRA user.

———————–

set serveroutput on;

———————–

@ /u01/app/oracle/stage/PurgeDir/soa_purge12/common/debug_on.sql

—————————————————————-

DECLARE

max_creation_date timestamp;

min_creation_date timestamp;

batch_size integer;

max_runtime integer;

retention_period timestamp;

PQS integer;

DOP integer;

max_count integer;

ignore_state boolean;

 BEGIN

 min_creation_date := to_timestamp(‘2020-01-01′,’YYYY-MM-DD’);

max_creation_date := to_timestamp(‘2020-03-31′,’YYYY-MM-DD’);

batch_size :=100000;

max_runtime := 120;

retention_period := to_timestamp(‘2020-03-31′,’YYYY-MM-DD’);

ignore_state := false;

PQS := 4;

DOP := 2;

max_count := 1000000;

soa.delete_instances_in_parallel(

min_creation_date => min_creation_date,

max_creation_date => max_creation_date,

batch_size => batch_size,

max_runtime => max_runtime,

retention_period => retention_period,

DOP => DOP,

max_count => max_count,

purge_partitioned_component => false,

ignore_state => ignore_state,

sql_trace => true);

 END;

/

——————————————————–

10. Use below to check background job status.

SELECT owner, job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;

11. Once background jobs are completed, run Step-8 again (to check the count after purge), See session output and debug logs from “/u01/app/oracle/stage/PurgeDir/soa_purge12/purge” for more details.