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
In this example, the AUD$ table is about 29 GiB.
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)
Database : 11.2.0.4,12.1.0.1
OEM : 13.3.0.0.0
Tags In
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)