- 3,769,280 Users
- 504,873 Discussions
- 1,613,213 Comments
Can you shrink AUD$ without causing blocking sessions (i.e. outage)
We have a situation where it would appear the audit purge process is not honoring our batch sizes. Haven't figured out why but I have a SR open for it (maybe I'll get lucky on an SR).
Since the purge process was throwing as giant number in the delete statement:
DELETE FROM SYS.AUD$ WHERE DBID = 382813123 AND NTIMESTAMP# < to_timestamp('2020-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF') AND ROWNUM <= 140724603553440
It got to the point where it was failing due to not enough undo space. We let that go far longer than we should thus AUD$ grew quite large. I've since manually cleared about 40% of the records using the delete above with a more reasonable rownum value. But now I want to reclaim that space with the assumption that I'll eventually figure out why the purge job isn't using the configured DB_DELETE_BATCH_SIZE.