How to purge web service instance data from the SOA repository database
a) Firstly you will need to look up the user name prefix of your SOA users:
b) If you have version 22.214.171.124 of the SOA suite (i.e. exor version 4.5.10 or earlier) then you will need to install the Oracle RCU home directory. This can be obtained from the Oracle website, or you can obtain a copy from us by raising a new service request. The Oracle RCU pack is already included within later releases of the SOA suite, which is used for exor version 126.96.36.199 or later. Personally I prefer to extract the RCU home directory on the database server as this already has SQLPlus installed and the required TNSNAMES.ora file entry.
c) Connect to the SOA Repository DB with SQLPlus as SYSDBA and grant the following privileges to the SOA Infra user (noted in the first step):
d) Exit SQLPlus
e) Change directory to the soa_purge directory within the RCU home. For example:
f) Connect to the DB with SQLPlus as the SOA Infra user noted in the first step (i.e. TMA_SOAINFRA) and run the following script. This should create some procedures, functions, types and packages under <user prefix>_SOAINFRA schema.
g) Before running the purge you can check how many records could be purged using below SQL. Please note cube_instance is not the only table which gets purged, there are a number of child tables which are purged as well.
h) If you want to spool the PLSQL program's output to a log file then set serveroutput on. This would help you understand which tables and eligible records are purged.
SQL> SET SERVEROUTPUT ON;
SQL> spool '/tmp/spool.log'
Once you have run the following purge script remember to run the ‘spool off’ command.
i) You are now ready to purge the instance data, but there are 2 methods to choose from, either loop purge or parallel purge. In loop purge it iterates through the set of eligible records and purges them. 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). Below is a sample of loop purge, for parallel purge the procedure name is delete_instance_in_parallel.
min_creation_date := to_timestamp('2008-10-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2014-07-26','YYYY-MM-DD');
retention_period := to_timestamp('2014-08-26','YYYY-MM-DD');
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => 10000,
max_runtime => 60,
retention_period => retention_period,
purge_partitioned_component => false);
j) Once the script completes and you run the spool off command, you can re-run use the SQL from step g to check how many records were purged and also open the spool.log to see the data purged from child tables.
How to Limit Log File Numbers and Sizes: