Our RSA Professional Services Rep advised that I contact support to obtain scripts to perform a database clean up. Our DB has grown to almost a terabyte in size, and this is potentially root cause for some of long running queries, such as when someone attempts to review the requests linked to a specific persons profile.
Where can I find these scripts?
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:
The following are example commands for the above types of data respectively:
Any cleanup like this is good practice and helping reduce data in the system that isn’t needed helps performance.