THE FAILED LOGINS METRIC QUERY IS VERY SLOW BECAUSE OF SYS.AUD$ TABLE SIZE

SYSTEM tablespace is too large. The top segment of SYSTEM tablespace is AUD$ table.

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 OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(BYTES/1024/1024) MB 
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME='SYSTEM' 
ORDER BY BYTES DESC
FETCH FIRST 5 ROWS ONLY;

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

In this example, the AUD$ table is about 29 GiB.

The AUD$ table is used for the database auditing purpose (CREATE SESSION, LOGON, LOGOFF, SELECT, INSERT, DELETE) when AUDIT_TRAIL is set either to DB or to DB_EXTENDED. 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

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

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

1 Delete all rows by TRUNCATING table

TRUNCATE TABLE SYS.AUD$;

2 Purge audit trail records by issuing 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;/

3 Delete specific subset of records between 2 days by issuing the DELETE command

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

4 Schedule a clean up job by following the section Create and Schedule the Purge Job

If you have no need of having database auditing just disable it. 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;

How to Truncate, Delete or Purge Rows from SYS.AUD$ (Doc ID 73408.1)
SCRIPT: Basic example to manage AUD$ table 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)

Written At

07 SEPTEMBER 202014:30

Red Hat Release

7.x x64

Database Release

11.2.0.4

Database Release

12.1.0.1