A couple of questions:

What are you checking when you say you have 50GB of audit data? The table has three CLOB columns, and many people forget to add in the size of the LOB segments when they report the table size.

John's comment about chained rows is highly significant in this case - the varchar2() columns in the aud$unified table have a total length declaration of a little over 70KB; it's not likely that any rows actually use that much space, but the rows could still be very long if you've enabled any interesting features of Oracle. On top of that, the CLOBs are all declared "enable storage in row" - one is about the RLS (FGAC) information, one is the SQL_TEXT, and one is the SQL_BINDs, so in many cases you might have code that squeezes below the 4000 byte limit that allows the CLOB to stay in the row. Bottom line - chained rows will not go away if you rebuild the table.

(It's also possible that the 49pct reclaimable space is NOT reclaimable - just because it's Oracle's own space advisor that's reporting it there's no good reason to assume the code is accurate.)

What is your current retention, and what did you reduce it from - it's possible to imagine a pattern where (e.g.) you had 9 months of audit stored and reduced it to one month - but that might mean you have 400GB (8 months) of empty space at the start of the file(s) and 50GB at the ends of the file, blocking the shrink. Maybe you just need to wait a while until Oracle has started reusing space the start of file for the next partition(s) and the time comes when you (or the admin package) can drop the partitions that are currently blocking the shrink.

There is an odd glitch in the "clean_audit_trail" procedure, by the way. If you give it a timestamp it deletes all data prior to that timestamp - but it's clever enough to drop any complete partitions that have a high_value less than that timestamp; however, if you give it a timestamp that is exactly the high_value for a partition the code deletes all the data in the partition when it could drop the partition - so you need to add one second to the high_value if you want to drop the partition rather than working hard to delete everything.

Regards

Jonathan Lewis