➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
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!