SYS.AUD$ size

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the size of the SYS.AUD$ table in Oracle databases, ensuring it does not fill up excessively with data.
• Why: This job is important because if the SYS.AUD$ table becomes too large, it can potentially affect the performance of the database or lead to storage issues. Monitoring alerts administrators before reaching critical thresholds, allowing for timely intervention.
• Manual checking: To check the size of SYS.AUD$ manually, use the SQL command:

select round(bytes/1048576,1), (select count(*) from sys.aud$) from dba_segments where segment_name='AUD$' and owner='SYS';

Monitoring Logic

The job operates according to the following logic:

• First, it checks if there are entries in the DBA_SEGMENTS table (specific to Oracle databases) for access privileges.
• The job queries the database to determine the size of the AUD$ table and the count of rows it contains.
• Warnings or alarms are raised based on predefined thresholds for the size: a warning at 100 MB and an alarm at 500 MB.

Job Implementation

The job leverages SQL and JavaScript script processing:

• “SQL part:” Gathers data regarding the size of SYS.AUD$ in megabytes and counts the number of lines (records).
• “JavaScript part:” Applies logic to compare the fetched size against predefined thresholds and sets the job status based on these comparisons—resulting in either normal status, a warning, or an alarm.

Job Configuration Details

• Default Schedule: Runs every 3 hours.
• Compatibility: Specifically designed for Oracle instances that are write-enabled but do not have a specific ENGINE attribute (hasengine=‘NO’).
• Thresholds:
  • Warning Threshold: 100 MB
  • Alarm Threshold: 500 MB

Reporting

A report template includes:

• Version: 2
• Title & Description: Display the SYS.AUD$ size status
• Schedule: Potentially rerun every hour
• Content: Presents a table displaying the current size and row count of SYS.AUD$, helping database administrators visualize the status at a glance.

Output Examples and Usage

• Usage: Intended for database administrators to ensure that the SYS.AUD$ table does not unexpectedly increase in size, risking database performance.
• Sample Output from Monitoring (hypothetical): If the SYS.AUD$ size reaches 150 MB with 3000 entries, and the warning threshold is at 100 MB, the job would raise a warning status indicating the need for attention.