How do I truncate the sys.aud$ table on my Amazon RDS DB instance that's running Oracle?

Resolution

To truncate the sys.aud$ table, run the following command as the master user:

SQL> exec rdsadmin.rdsadmin_master_util.truncate_sys_aud_table;

Note: You must run the TRUNCATE_SYS_AUD_TABLE procedure as a master user. Oracle versions 12.1.0.2.v2, 11.2.0.4.v6, and later versions support this operation.

The following is an example of the output:

PL/SQL procedure successfully completed.
SQL> select count(*) from sys.aud$;
 COUNT(*)----------
         0

If the preceding command fails, then run the following commands to determine the issue:

To check whether the TRUNCATE_SYS_AUD_TABLE procedure is available on your RDS DB instance, run the following command:

SQL> desc rdsadmin.rdsadmin_master_util

If your RDS DB instance has the TRUNCATE_SYS_AUD_TABLE procedure, then you receive a response that's similar to the following one:

FUNCTION IS_DML_ENABLED RETURNS BOOLEAN
PROCEDURE TRUNCATE_SYS_AUD_TABLE
PROCEDURE TRUNCATE_SYS_FGA_LOG_TABLE

To check whether the RDS_MASTER_ROLE role is available on your RDS DB instance, run the following command:

SQL> select role from dba_roles where role='RDS_MASTER_ROLE';

If the RDS_MASTER_ROLE role is available on your RDS DB instance, then you receive a response that's similar to the following one:

ROLE--------------------------------------------
RDS_MASTER_ROLE

To verify that the master user has permissions to run the TRUNCATE_SYS_AUD_TABLE procedure, run the following command:

SQL> select granted_role, grantee, admin_option from dba_role_privs where granted_role='RDS_MASTER_ROLE';

If the master user has permissions to run the TRUNCATE_SYS_AUD_TABLE procedure, then you receive a response that's similar to the following one:

GRANTED_ROLE         GRANTEE              ADM
-------------------- -------------------- ---
RDS_MASTER_ROLE      SYS                  YES
RDS_MASTER_ROLE      MASTER_USER          NO

After you determine what the issue is, contact AWS Support for assistance.

Related information

Amazon RDS for Oracle

Administering your RDS for Oracle DB instance