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

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

SQL> exec rdsadmin.rdsadmin_master_util.truncate_sys_aud_table;

Truncating the table requires that your DB instance have the ability to run the TRUNCATE_SYS_AUD_TABLE procedure as a master user. Oracle versions that are known to support this operation are 12.1.0.2.v2 and 11.2.0.4.v6, as well as subsequent versions.

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.    First, use 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'll receive output similar to the following:

FUNCTION IS_DML_ENABLED RETURNS BOOLEAN
PROCEDURE TRUNCATE_SYS_AUD_TABLE
PROCEDURE TRUNCATE_SYS_FGA_LOG_TABLE

2.    Next, 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'll receive output similar to the following:

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

3.    Last, verify that the master user has permissions to execute the TRUNCATE_SYS_AUD_TABLE procedure by running the following commands in sequence:

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

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

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

RDS, Oracle, sys.aud$, truncate, table


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2016-1-27