Urge to Purge part 2

Part 1 of this blog post discussed using standard purge functions to reduce overhead on the system. A query to identify the largest objects was provided as a starting point for purge investigations.

A couple of other items are worth reviewing. Often systems will have backup tables created to assist with troubleshooting. These tables should be removed when they are no longer needed.

One way to locate backup tables is to run a query like the following. It may not catch all tables depending upon the naming standards, but it is a good place to start. The list that gets returned may include valid tables, so be sure to check to determine if the tables are being used. You can also edit this query to use other possible strings such as TEMP, TMP, ARCH, ARCHIVE, HIST, BACK, OLD, etc.

select * from dba_segments
where owner not in ('SYS','SYSTEM')
and segment_name like '%BKP%'
and segment_type='TABLE'
order by bytes desc;

Another place to review, involves the log files for materialized views. Sometimes these logs can become quite large. There are a few options to reclaim the space, review Oracle notes for cleaning up materialized view logs.  If you are using E-Business Suite, look up the view name in My Oracle Support as there may be additional steps needed to reclaim the space. The following query will list the materialized view logs ordered by size. Focus on only the largest objects.

select *
from dba_segments
where segment_name like 'MLOG$_%'
order by bytes desc;