How do I truncate the sys.aud$ table on my Amazon RDS DB instance that is running Oracle?
Last updated: 2020-09-25
Resolution
SQL> exec rdsadmin.rdsadmin_master_util.truncate_sys_aud_table;
PL/SQL procedure successfully completed.
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
0
Note: Truncating the table requires that your RDS 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 needs to provide, run the following commands and then note their output:
1. Run the following command to determine if the TRUNCATE_SYS_AUD_TABLE procedure is available on your RDS DB instance:
SQL> desc rdsadmin.rdsadmin_master_util
FUNCTION IS_DML_ENABLED RETURNS BOOLEAN
PROCEDURE TRUNCATE_SYS_AUD_TABLE
PROCEDURE TRUNCATE_SYS_FGA_LOG_TABLE
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 output similar to the following:
ROLE
--------------------------------------------
RDS_MASTER_ROLE
3. Run the following command to verify that the master user has permissions to run the TRUNCATE_SYS_AUD_TABLE procedure:
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 output similar to the following:
GRANTED_ROLE GRANTEE ADM
-------------------- -------------------- ---
RDS_MASTER_ROLE SYS YES
RDS_MASTER_ROLE MASTER_USER NO