Purging and partitioned schemas

SOA Suite 11g and 12c both require regular database maintenance for optimal performance. A key task in managing your SOA Suite database is a regular purging strategy. You should be doing this, so read the Oracle SOA Suite database growth management strategy if you haven’t already: 

One of the best practices for managing large SOA Suite applications is to use Oracle Database partitioning. In 11g this is usually a fairly ad-hoc setup, though the whitepaper has everything you need to know about setting it up; in 12c, the “LARGE” RCU profile is partitioned (with monthly partitions).Purging a partitioned schema usually involves running the check and move scripts, to ensure your partitions don’t contain “LIVE” data (based on your retention policy), followed by dropping the “OLD” partitions and rebuilding the indexes.However, there are times where you may want to run a purge to clean up data that doesn’t neatly align with the partitions, for example in a load testing environment. 

      The purge scripts, by default, won’t touch any table that is partitioned. If your favourite table isn’t mentioned in the purge debug log output (example below), then it is probably because it is partitioned.

To force the purge scripts to consider partitioned tables, you need to enable the “purge_partitioned_component” flag to the “delete instances” purge function (see below). The purge script will then purge partitioned tables.Obviously, this is not intended for regular production use and it should never be used there.

An example invocation with the flag set:
soa.delete_instances(max_runtime => 300, min_creation_date => to_timestamp('2000-01-01','YYYY-MM-DD'), max_creation_date => to_timestamp('2000-12-31','YYYY-MM-DD'), purge_partitioned_component=TRUE);

The example output below is from a soa.delete_instances run that has a partition on composite_instance. Note that there is no mention of composite_instance in the output.
There are several tables which can be partitioned, as well as whole units (such as BPEL). The purge script will skip any that have a partition. (If you are interested, you can search the PLSQL packages in a SOAINFRA schema for ‘is_table_partitioned’ to see which tables are checked and which columns it considers for partitioning).01-JAN-2000 12:00:00 : procedure delete_instances