Purging and partitioned schemas by Christian

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: http://www.oracle.com/technetwork/middleware/bpm/learnmore/soa11gstrategy-1508335.pdf

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. Read the complete article here.

SOA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit www.oracle.com/goto/emea/soa (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki

About Jürgen Kress
As a middleware expert Jürgen works at Oracle EMEA Alliances and Channels, responsible for Oracle’s EMEA Fusion Middleware partner business. He is the founder of the Oracle SOA & BPM and the WebLogic Partner Communities and the global Oracle Partner Advisory Councils. With more than 5000 members from all over the world the Middleware Partner Community is the most successful and active community at Oracle. Jürgen manages the community with monthly newsletters, webcasts and conferences. He hosts his annual Fusion Middleware Partner Community Forums and the Fusion Middleware Summer Camps, where more than 200 partners get product updates, roadmap insights and hands-on trainings. Supplemented by many web 2.0 tools like twitter, discussion forums, online communities, blogs and wikis. For the SOA & Cloud Symposium by Thomas Erl, Jürgen is a member of the steering board. He is also a frequent speaker at conferences like the SOA & BPM Integration Days, JAX, UKOUG, OUGN, or OOP.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: