DELETE queries failing on AUDIT_EVENT table

Document ID : KB000111033
Last Modified Date : 14/08/2018
Show Technical Document Details
Issue:
The housekeeping which should run deletes every night is failing to complete, so the AUDIT_EVENT table has grown to
an unmanageable size, containing many millions of rows.
How can we get the housekeeping running successfully again ?
Resolution:
It has been noted that once the AUDIT_EVENT table starts to go into millions of rows, the housekeeping will likely fail due to the time taken for the queries to complete.
Deletes that would normally complete in a reasonable period of time (a few seconds to a few minutes) will never complete.
Attempting to run manual deletes against the table will also likely fail. 

In this situation, the only option is to backup both AUDIT_EVENT and AUDIT_CONFIG_CHANGE tables then drop and recreate both.
There is a relationship on the event_id column between the two tables, so attempting to drop one without dropping or otherwise cleaning out the other will break the referential integrity.
Both tables must be cleaned up.