THE AUD$ TABLE IS VERY LARGE

 
SYMPTOMS

SET PAGES 999
SET LINES 300
COL OWNER FOR A10 
COL SEGMENT_NAME FOR A20 
COL SEGMENT_TYPE FOR A15 
COL MB FOR 9999999
SELECT * FROM (
SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(BYTES/1024/1024) MB 
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME='SYSTEM' 
ORDER BY MB DESC)
WHERE ROWNUM < 5 ;

OWNER      SEGMENT_NAME         SEGMENT_TYPE          MB
---------- -------------------- --------------- --------
SYS        AUD$                 TABLE              28780
SYS        IDL_UB1$             TABLE                311
SYS        SOURCE$              TABLE                 80
SYS        IDL_UB2$             TABLE                 40

 
 
CAUSE

 
The AUD$ table is used for the database auditing purpose (CREATE SESSION, LOGON, LOGOFF, SELECT, INSERT, DELETE) when AUDIT_TRAIL is set to either DB or DB_EXTENDED value. Starting from 11g the AUDIT_TRAIL is activated by default and the parameter is set to DB.

SQL> SELECT * FROM V$VERSION WHERE BANNER LIKE '%Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> sho parameter AUDIT_TRAIL

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

 
 
SOLUTION

 
The proper solution is to regularly purge the SYS.AUD$ table. There are serveral options

1) By regularly truncating SYS.AUD$ table.

TRUNCATE TABLE SYS.AUD$;

 
or

2) You can purge the audit trail records by running the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure.

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE );
END;
/

 
or

3) You can delete the audit trail records for a specific period of time

For example, to delete audit records that were created later than the evening of February 28, 2009 but before March 28, 2009, enter the following statement:

DELETE FROM SYS.AUD$
   WHERE NTIMESTAMP# > TO_TIMESTAMP ('28-FEB-09 09.07.59.907000 PM') AND
   NTIMESTAMP# < TO_TIMESTAMP ('28-MAR-09 09.07.59.907000 PM');

 
or

4) You can schedule the clean up job. To do so follow the steps of the section Create and Schedule the Purge Job

NOTE: SYS.AUD$ and SYS.FGA_LOG$ are the only SYS objects that can ever be directly modified.

 
As additional optional option if you have no need for the database auditing is disabling the database auditing. The instance restart is required.

SQL> SHOW PARAMETER audit_trail

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

SQL> ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

 
 
REFERENCES

 
How to Truncate, Delete or Purge Rows from SYS.AUD$ (Doc ID 73408.1)
SCRIPT: Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt (Doc ID 1362997.1)
Sys.aud$ Issue In OUA DB (Doc ID 2234067.1)
All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault (Doc ID 207959.1)