Urge to Purge part 1

Purge is a four letter word in many organizations. Resistance to purging data is understandable. The technical staff will be at odds with the functional groups if there is ever a time when needed data is no longer available.

The penalty against purging is performance degredation and additional storage requirements. The additional storage also has an adverse effect on backup and recovery operations. In addition to purchasing additional storage, companies often have to purchase additional CPU which can also impact licensing costs.

If you're not sure of the best place to start your purge investigation, for EBS customers you should be purging as much of the Concurrent Request histor and Workflow history as possible. Next you should investigate the purging options for the features that are most heavily used at your site.

If you are unsure of which modules result in excessive storage usage, then check for the largest objects in your database. Based upon the tables returned, you can then use My Oracle Support to check for appropriate purge routines.

A simple query to check for the 50 largest objects:
select owner,segment_name, segment_type, bytes
from (select owner,segment_name, segment_type, bytes, rank() over
(order by bytes desc
) as rank from dba_segments
) where rank <=50;