Hi everyone,
I am deep-diving into database audit management and currently troubleshooting an advanced optimization issue regarding the purging of the AUD$UNIFIED table via partition dropping.
Context & Problem: My AUD$UNIFIED table is partitioned daily. As we all know, row-by-row DELETE operations during audit purges are extremely slow and resource-intensive on heavy production environments. I am trying to force the system to perform a metadata-level **DROP PARTITION** instead of a standard DELETE.
Based on several expert Oracle blogs, a well-known workaround to trigger the partition drop execution plan is to set the last_archive_timestamp slightly higher (by a few milliseconds or seconds) than the HIGH_VALUE of the targeted partition. For instance, if the partition's HIGH_VALUE is 15-JUN-2026 00:00:00, setting the timestamp to 15-JUN-2026 00:00:00.001 should theoretically allow DBMS_AUDIT_MGMT to safely drop the entire underlying partition.
However, despite multiple test iterations and adding the recommended time offset, my system consistently defaults to a row-by-row **DELETE** execution plan.
Evidence Attached:
- Screenshot 1: Shows my exact
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP call with the time-offset increment, followed by the invocation of DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.
- Screenshots 2 & 3: Captured via TOAD, showing the Current Statement and the Execution Plan during the purge process. It explicitly shows a
DELETE operator being executed rather than a Partition Drop/Truncate mechanism.
My Goal: I need to find a way to drop by partition and understand what internal constraints are preventing Oracle from choosing the Partition Drop execution path in this scenario. My ultimate objective is to guarantee a lightning-fast metadata drop rather than a costly row deletion.
Any insights into internal parameters, undocumented behaviors, or hidden pre-requisites (e.g., specific index status or underlying SYS_IL LOB partition dependencies) that govern this CBO decision would be highly appreciated.
Thank you in advance for your valuable support!