Skip to Main Content
  • Questions
  • POUR performance on CLEANUP/PURGE SYS.$AUD


Question and Answer

Connor McDonald

Thanks for the question, Alireza.

Asked: September 29, 2023 - 4:15 pm UTC

Last updated: October 04, 2023 - 1:22 am UTC


Viewed 1000+ times

You Asked

Hi Tom,
we need to purge, ARCHIVE and cleanup our SYS.$AUD table.
WE do have a RAC x 2 node as an Active DG too.

We have a self-defined procedure , which is working appropriately (using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL).
However the table is >700GB and >700 Mil rows.
The procedure runs everyday and retrieves the data from $AUD -> makes a CTAS and inserts the data in regular table based on the date range --> then the appropriate date range would be purged and cleaned up.

SQL> select /*+ PARALLEL(64) */ to_char(ntimestamp#,'YYYYMMDD') as date_aud , COUNT(0) row_num from sys.aud$ group by to_char(ntimestamp#,'YYYYMMDD') ORDER BY to_char(ntimestamp#,'YYYYMMDD');

a partial selection of AUD table:
-------- ----------
20230712 4722355
20230713 4748860
20230714 4231487
20230715 4360975
20230716 4173837
20230717 5148050
20230718 5414122
20230719 4281115
20230720 4599951
20230721 4002760
20230722 3982341
78 rows selected

We use also the max bacth size (1M) for cleanup
SELECT * FROM dba_audit_mgmt_config_params;

The issue is that the CLEANUP/ARCHIVE is very I/O intensive and takes 4-5 hours to cleanup/archive the data for a single date range, and we can run max 2 times a day (from 12pm to 12am) as some very I/O intensive ETL jobs run before and after this time period.
We also tried to MOVE the TBS to a new ONE , to have new segment optimized TBS for a better performance using:

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'NEW_TBS');

But the MOVE process (in a down time) takes more than 20hrs , which is not affordable in a PROD system.
WE opened also 2 SRs with no success on an acceptable result in terms of I/O.

Also the AUDIT data is getting produced every day , so that we are running behind, but with every daily CLEANUP we get new data so that it takes forever to catch up.

Unless you recommend any other approach to achieve this challenge? we do have also a snapshot DG in case you have an idea.
Pls let me know if any info missing or need more INPUT.

Thank you very much!

and Connor said...

I'm going to propose a different route here - I would stop working with AUD$ and migrate to unified auditing.

Some good info here


The reason for my recommendation is that the base tables for *unified* auditing are partitioned, and thus when you come to do cleanup activities the DBMS_AUDIT_MGMT routine can take advantage of partition-wise operations to remove old data.

So during your next maintenance cycle, activate unified auditing, take a final archival of all of the data in AUD$ and then truncate it, and move forward with unified auditing.


  (2 ratings)


Sr. oracle dba

Alireza Mirbakhtiar, October 03, 2023 - 4:15 pm UTC

Hi Connor,
thank you for you hint, however (just in case our customer prefers to remain by standard auditing) , can the $AUD table get truncated after the data is archived?
the purpose is to TRUNCATE the table and resize the Tablespace (not SYSAUX) ,, thus the tablespaces would be set with optimized segments and the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL operations run much faster. The reason i am asking is, because support DOES not recommend the TRUNCATE on this table.

Connor McDonald
October 04, 2023 - 1:21 am UTC

In normal operation we always recommend the use of DBMS_AUDIT_MGMT (as per Support), but for specific issues you should be able to get their blessing for a one-off use of truncate (most probably during an outage)

Sr. oracle dba

Alireza Mirbakhtiar, October 03, 2023 - 4:54 pm UTC

... also need to know if unified auditing requires any advanced license? or it is included feature of EE 19c?

Thank you!
Connor McDonald
October 04, 2023 - 1:22 am UTC

Unified auditing is not part of a separate license

For future reference - a nifty tool

More to Explore


All of the vital components for a secure database are covered in the Security guide.