Database Clean-Up Scripts for IMG? (Ver 6.9.1 Patch 16)

I would recommend you start by using the stored procedure package Data_Retention_Pkg to help cleanup the database.  You may also need to use Oracle to shrink and consolidate space.

The data pruning is a stored procedure that you can choose to call at whatever frequency and time period your company chooses (we don’t enforce a retention policy as each company has their own).

In later releases the product does schedule this cleanup on a regular basis and it is surfaced more in the UI.  In the release you have, you can either invoke it manually from your favourite SQL tool or some customers will create a cron script so it calls sqlplus to do the commands on some frequency.

In terms of the calls to make, I would suggest starting with a small window to test and then open it up more.  This also ensures you don’t create large redo logs and changes are committed after each call.  By default the calls also shrink the tables after removing data to help cleanup Oracle’s storage management.

The type of data it cleans up includes:

• Cleaning up unneeded runtime data for change requests (no audit information is removed)
• Clean up debug logging – this removes entries that aren’t needed in the long term
• Cleanup run details – this removes anything that is not the summary or pp_summary entries that I mentioned below
• Remove old run history – this actually removes older runs from the product that you see on the Admin->Monitoring page.  Usually you don’t need to keep the history about collections you did two years ago for example
• Remove old events – This is good hygiene where it removes any events that are completed (nothing in the UI depends on these events and they can be safely removed if completed)

The following are example commands for the above types of data respectively:

• execute DATA_RETENTION_PKG.PRUNE_ACCESS_REQUEST(IN_OLDER_THAN => ‘11-SEP-2016');
• execute DATA_RETENTION_PKG.PRUNE_APPLICATION_LOG(IN_OLDER_THAN => ‘11-SEP-2016');
• execute DATA_RETENTION_PKG.PRUNE_RUN_DETAILS(IN_OLDER_THAN => ‘11-SEP-2016');
• execute DATA_RETENTION_PKG.PRUNE_RUN_HISTORY(IN_OLDER_THAN => ‘11-SEP-2016');
• execute DATA_RETENTION_PKG.PRUNE_EVENTS(IN_OLDER_THAN => ‘11-SEP-2016');

Any cleanup like this is good practice and helping reduce data in the system that isn’t needed helps performance.