My Oracle Support Banner

Deleting Records From SYS.AUD$ When Connected as Another User Than SYS Deletes Less Rows Than Selected (Doc ID 388169.1)

Last updated on FEBRUARY 23, 2023

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

This problem can occur on any platform.


A user X granted SELECT and DELETE on SYS.AUD$ table by SYS user selects a number of rows from SYS.AUD$ table but cannot delete any rows or only some of them, for example:

SQL> connect x/x
SQL> select count(*) from sys.aud$;


When the user connected is SYS, the number of rows deleted is 1200. 

SQL> delete from sys.aud$;
0 rows deleted.


In RDBMS version 10.2 Oracle Development implemented a security enhancement that mandates that DML on the audit trail (SYS.AUD$) must always be audited and that those records cannot be deleted other than by user SYS.

Note that this rule is also enforced even if SYS creates a stored procedure that deletes the rows from SYS.AUD$ with definer's rights and grants execute on this procedure to another user, this will not work.

The only thing that would work is to create a procedure in the SYS schema which would truncate table AUD$ :

This would work because TRUNCATE is considered a DDL and not a DML.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.