Database Administration (MOSC)

MOSC Banner


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.


Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.


Don't have a My Oracle Support Community account? Click here to get started.

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center