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

Last updated: 2019-04-17

How do I truncate the sys.aud$ table on my Amazon Relational Database Service (Amazon RDS) instance that is 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;

If the procedure is successful, you receive output similar to the following:

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

Note: Truncating the table requires that your DB instance can run the TRUNCATE_SYS_AUD_TABLE procedure as a master user. Oracle versions 12.1.0.2.v2 and 11.2.0.4.v6, as well as subsequent versions, support this operation.

If the preceding command is unsuccessful, contact AWS Support for assistance. To determine what kind of assistance AWS Support will need to provide, run the following commands and note their output:

1.    Run the following command to determine if the TRUNCATE_SYS_AUD_TABLE procedure is available on your DB instance:

SQL> desc rdsadmin.rdsadmin_master_util

If your DB instance has the TRUNCATE_SYS_AUD_TABLE procedure, you receive output similar to the following:

FUNCTION IS_DML_ENABLED RETURNS BOOLEAN
PROCEDURE TRUNCATE_SYS_AUD_TABLE
PROCEDURE TRUNCATE_SYS_FGA_LOG_TABLE

2.    Run the following command to determine if the RDS_MASTER_ROLE role is available on your DB instance:

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

If the RDS_MASTER_ROLE role is available on your instance, you receive output similar to the following:

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

3.    Verify that the master user has permissions to run the TRUNCATE_SYS_AUD_TABLE procedure by running the following commands in sequence:

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

If the master user has permissions to run the TRUNCATE_SYS_AUD_TABLE procedure, you receive output similar to the following:

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

Oracle on Amazon RDS

Common DBA Tasks for Oracle DB Instances

Did this article help you?

Yes
No

Anything we could improve?

Let us know

Need more help?

Contact AWS Support