Truncate AUD$ table in Oracle 12c

Truncate AUD$ table in Oracle 12c | In this post, we will learn how to truncate the AUD$ tables in oracle 12c. SYS.AUD$ table which is referred to as the “audit trail” is a table inside the sys schema and stores database auditing information.

There will be a question arising that is it safe to truncate the AUD$? Yes, it is safe to truncate AUD$ as it consumes too much space, so we can truncate the AUD$ as soon as possible.

To show/view/modify the AUD$ table we have to log in as administrators then only we can access this table.

SQL> conn sys as sysdba
Enter password: sys
Connected.

To check whether the audit is enabled or disabled we will execute the below query.

SHOW parameter AUDIT trail;

Output:-

NAME         TYPE        VALUE
----------  -------- --------
audit_trail   string      DB

Now let us see how to truncate the aud$ table by using the following query.

TRUNCATE TABLE SYS.AUD$;

The table will be truncated OR when there are too many long-running queries, we will get the output as below.

Output:-

ERROR: ORA-00176: resource busy and acquire with NOWAIT …

So, let us shut down it immediately and restart the database by using the following query.

SHUTDOWN IMMEDIATE;
STARTUP;

We have to export the audit table backup. Then we will truncate using the following query.

TRUNCATE TABLE SYS.AUD$;

This is how we truncate the sys.AUD$ in oracle 12c. It will delete all the data from the sys.AUD$ table. Also see:- Drop All Tables Oracle

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *