Mir Sayeed Hassan – Oracle Blog

Deleting the SYS.AUD$ Table in Oracle Database

Find the below procedure:

Before deleting the SYS.AUD$ table, take the backup by using the exp utility

Note:

In Oracle 11g we cannot run expdp to export the SYS.AUD$ table, Use the exp utility

Create the directory in OS & DB Level:

[oracle@testdb backup]$ mkdir dump_bkp

[oracle@testdb backup]$ chmod –R 775 dump_bkp

sys@TESTDB> create the directory dump_bkp as ‘/backup/dump_bkp’;
 Directory created

sys@TESTDB> grant read,write on directory dump_bkp to system
 Grant succeeded

Verify the count of sys.aud$ table

[oracle@testdb ~]$ sqlplus

sys@TESTDB>select count(*) from sys.aud$;

COUNT(*)
 --------
 158731268

sys@TESTDB> show parameter audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /u01/app/oracle/admin/prim/adu mp

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

Export the table using exp utility

[oracle@testdb dump_bkp]$ exp system file=dump_sysaud_bkp tables=sys.aud$

Export: Release 11.2.0.4.0 - Production on Mon Jun 11 12:06:36 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Password: *******

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

Current user changed to SYS

. . exporting table AUD$ 158731268

sys@TESTDB>noaudit all;

Noaudit succeeded.

sys@TESTDB> truncate table sys.aud$;

Table truncated.

sys@TESTDB> select count(*) from sys.aud$;

COUNT(*)
--------
0

Purging audit from SYS.AUD$ table:

Disabling Audit:-

Noaudit Table;
Noaudit All Privileges;

Turn Off All Auditing:-

Noaudit All;
Noaudit All Privileges;
Noaudit All On Default;

Purge Auditing:-

Delete From Sys.Aud$;
Truncate From Sys.Aud$

Object level delete:

Delete From Sys.Aud$ Where Obj$Name=’mir’;

===========Hence resolved in our test env==============