Since the start on fusion we have been plagued with purge related issues, 11.1.1.3 the purge job barely worked.So we applied patches on top of it to fix it.On 11.1.1.5 its far better.So i will talk W.R.T 11.1.1.5 or higher version of SOA,
There are 2 options for purging SOA Tables :
1) Looped Purge - The stored procedure soa.delete_instances provides the looped purge implementation. Debug mode can be enabled by executing soa_purge/common/debug_on.sql and setting serverout on.
Sample client to execute looped purge is listed below.
2) Parallel Purge: The stored procedure soa.delete_instances_in_parallel provides the parallel purge implementation To execute the parallel purge, the following additional privileges are required for the soainfra user.Parallel purge is similar to loop purge with additional flexibility of spawning parallel threads to do the purging (faster, multi threaded approach if dealing with huge number of records)
i) Create Job
ii) execute on dbms_lock.
Debug mode can be enabled by executing the script 'soa_purge/common/debug_on.sql' and setting serverout on . The debug logs from the jobs spawned by parallel purge will be logged into files created in the directory 'SOA_PURGE_DIR' . This directory needs to be created(e.g: create directory SOA_PURGE_DIR as '\tmp\purge') and the soainfra user needs to be provided write access on this directory. Also ensure that the location of the directory is accessible to the oracle server.
There is another option ignore_state=> true ,which purges instances regardless of its state.Normally only completed instances are purged.
There are 2 options for purging SOA Tables :
1) Looped Purge - The stored procedure soa.delete_instances provides the looped purge implementation. Debug mode can be enabled by executing soa_purge/common/debug_on.sql and setting serverout on.
Sample client to execute looped purge is listed below.
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) Parallel Purge: The stored procedure soa.delete_instances_in_parallel provides the parallel purge implementation To execute the parallel purge, the following additional privileges are required for the soainfra user.Parallel purge is similar to loop purge with additional flexibility of spawning parallel threads to do the purging (faster, multi threaded approach if dealing with huge number of records)
i) Create Job
ii) execute on dbms_lock.
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;
Debug mode can be enabled by executing the script 'soa_purge/common/debug_on.sql' and setting serverout on . The debug logs from the jobs spawned by parallel purge will be logged into files created in the directory 'SOA_PURGE_DIR' . This directory needs to be created(e.g: create directory SOA_PURGE_DIR as '\tmp\purge') and the soainfra user needs to be provided write access on this directory. Also ensure that the location of the directory is accessible to the oracle server.
There is another option ignore_state=> true ,which purges instances regardless of its state.Normally only completed instances are purged.
ALso for 11.1.1.3 the script used is different.
ReplyDeleteIts as below,(run as soainfra user)
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB(
job_name => 'PURGE_COMPOSITES',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
DELETED_INSTANCES NUMBER;
BEGIN
FILTER.MAX_CREATED_DATE := SYSDATE - 7; /* Keep 7 days dehydration data */
DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => 100000,
PURGE_PARTITIONED_DATA => TRUE );
EXCEPTION
WHEN OTHERS THEN
NULL; /* TODO Exception Handling */
END;',
repeat_interval => 'FREQ=DAILY;',
start_date => to_timestamp('03/25/2011 23:00:00','MM/DD/YYYY HH24:MI:SS'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
COMMIT;
END;
/