When you work with Oracle’s out-of-the-box purge scripts, you’ll eventually realize that one of the things it does not perform is reclaiming table space after you execute a purge. This is a key factor when you’re trying to increase the overall performance with a SOA Dehydration Store. Thankfully, the hard part is realizing it is needed; it is easy to perform. You’ll want to generate a series of queries that reclaim the unused space within each table you are working with. Those queries are molded after the following:

One of the main focuses when reclaiming memory is tables relating to the Oracle BPEL Process Manager, which contains tables like CUBE_INSTANCE and XML_DOCUMENT.

Here are the before and after results when I tried these queries for the first time:

Before

After