Delete SYS.AUD$ data

Job details

Name:	Delete SYS.AUD$ data
Platform:	Oracle
Category:	Maintenance
Premium package:	Maintenance package
Description:	Deletes old rows from the SYS.AUD$ trail table.
Long description:	Deletes old rows from the SYS.AUD$ trail table. Configurable time delay in days.
Version:	1.1
Default schedule:	15 3 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & eleven_and_above=‘1′]/.[is_rds = ‘0′]

Name	Default value	Description
history threshold	30	Defines how many days we keep data for in the SYS.AUD$ table.
max rows to delete	10000	The maximum number of rows to delete per execution of the task.
max period of time to delete	30	The maximum number of days with trace statistics to delete.
enable delete operation	NO	Enables the task to delete data from SYS.AUD$ table.

Purpose: The purpose of this job is to manage and delete old audit records from the SYS.AUD$ table in Oracle instances that are version 11 and above, not hosted on RDS. This helps in maintaining the database size and improving performance by removing outdated audit data.
Why: This job is critical as it helps in managing database storage by regularly cleaning up the old audit data, which if unchecked, could grow significantly and degrade database performance. By automating this process, it ensures consistency and reduces the risk of human errors.
Manual checking: The operations carried out by this automated task can be manually executed in the database with the following SQL commands:

SELECT COUNT(*) FROM SYS.AUD$ WHERE ntimestamp# < sysdate - [configured days];

DELETE FROM SYS.AUD$ WHERE ntimestamp# < sysdate - [configured days];

This dbWatch job focuses on the delete operation for audit trails in the SYS.AUD$ table by creating procedures and configurations within the Oracle database system.
It operates under predefined criteria involving how many days of data to retain and the maximum amount of records or days of records to delete per execution.

Job Name: “Delete SYS.AUD$ data”
Operations:
- Procedure creation: dbw_aud$_delete_proc, dbw_del_aud, dbw_run_job

Default Schedule: Daily at 03:15 AM
The job execution schedule is designed to run at a time presumed to have minimal load on the database to minimize impact on database performance.

The task involves a sequence of procedures that:
- Check enablement status specified by “enable delete operation”.

Presentation Information:
- Reporting features are included in the job definition, providing details like execution dates, number of rows deleted, elapsed time, oldest and newest record deleted, etc.

Execution Date	Rows Deleted	Elapsed Time	Oldest Record Deleted	Newest Record Deleted	Rows Threshold	Period Threshold
date1	num1	time1	date-record1	date-record2	num2	num3
date2	num4	time2	date-record3	date-record4	num5	num6

The table will show the recent results of the deletion operations, providing a transparent view of the process effectiveness and timing, aiding in audits and checks.

This dbWatch job facilitates effective audit data management in Oracle databases, ensuring system health and compliance with data retention policies.