SOA Purge Scripts

 Check if AutoPurge job is runnig - 

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=301402729410603&parent=DOCUMENT&sourceId=2123653.1&id=2063039.1&_afrWindowMode=0&_adf.ctrl-state=2o1z1qf27_317

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=10681729155985&id=2745847.1&displayIndex=2&_afrWindowMode=0&_adf.ctrl-state=n1857oksm_157#REF

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=10715735917585&id=1563449.1&displayIndex=8&_afrWindowMode=0&_adf.ctrl-state=n1857oksm_312

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=10678348728630&id=2276923.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=n1857oksm_116

Purge working script - 

--select * from work_item where state = 1 and execution_type != 1;

DECLARE

CURSOR c_bpm_data

IS

select distinct(b.flow_id) from cube_instance b where ROWNUM <= 1000 and state = 5;

TYPE t_bpm_data IS TABLE OF c_bpm_data%ROWTYPE

INDEX BY BINARY_INTEGER;

l_bpm_data t_bpm_data;

l_id_string VARCHAR2(32767);

KEEP_WORKFLOW_INST BOOLEAN;

BEGIN

  l_bpm_data.DELETE;

  OPEN c_bpm_data;

  LOOP

  FETCH c_bpm_data BULK COLLECT INTO l_bpm_data LIMIT 400;

  EXIT WHEN l_bpm_data.count=0;

  l_id_string := NULL;

  

  FOR m IN 1..l_bpm_data.COUNT

  LOOP

     IF l_bpm_data(m).flow_id IS NOT NULL

     THEN

        l_id_string := l_id_string||','||l_bpm_data(m).flow_id;

   END IF;

END LOOP;

  l_id_string := l_id_string||','; 

  l_id_string := LTRIM(l_id_string,',');

 dbms_output.put_line(l_id_string);

-- KEEP_WORKFLOW_INST := FALSE;

SOA.DELETE_INSTANCES_ADHOC(v_flowid_purge => l_id_string);

END LOOP;

CLOSE c_bpm_data;

END;

========================

to verify --

select table_name from user_tables where partitioned = 'YES';

select state, count(*) from cube_instance group by state;

select count(*) from cube_instance where trunc(creation_date) <= '03-JUN-22' ;

select count(*) from cube_instance where trunc(creation_date) >= '29-MAY-22' ;

select  * from cube_instance where state = 5; group by state;

execute soa.delete_instances ( to_timestamp('2022-05-01','YYYY-MM-DD'),to_timestamp('2022-05-07','YYYY-MM-DD'),20000,60,to_timestamp('2022-05-07','YYYY-MM-DD'),false,null,null,null,true);

call soa.delete_instances(to_timestamp('2022-05-01','YYYY-MM-DD'), to_timestamp('2022-05-07','YYYY-MM-DD'));

select state, id, composite_dn from composite_instance where id = 7;

select distinct(flow_id) from cube_instance  where extract(YEAR from CREATED_ON) = 2018 ;

execute soa.delete_instances ( to_timestamp('2022-05-01','YYYY-MM-DD'),to_timestamp('2022-05-07','YYYY-MM-DD'),20000,60,to_timestamp('2022-05-07','YYYY-MM-DD'),false,true);

select * from soa_purge_history;

SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, INTERVAL FROM DBA_PART_TABLES WHERE OWNER LIKE '%SOAINFRA' ORDER BY 1,2;