I enabled the Oracle audit feature on my database to log events on the database audit tables, but the SYSTEM tablespace is growing more than expected, or I received the error messge "ORA-01653: unable to extend table..." for SYS.AUD$ or FGA_LOG$ in the SYSTEM tablespace. How do I fix this?

The ORA-01653 error is caused by a lack of free space on the SYS.AUD table in the SYSTEM tablespace, where the Oracle audit tables AUD$ and FGA_LOG$ are defined by default. The error occurs when the table attempts to auto-extend to accommodate additional data, but is prevented from doing so.

To resolve this issue, consider one or more of the following solutions:

Migrate the AUDIT tables to a dedicated tablespace

Audit tables grow indefinitely until either a manual or automated purging routine cleans up records by using DELETE statements. Because free space is only reclaimed to the affected segment, purging tables in the SYSTEM tablespace results in no functional change to the size of the AUD$ and FGA_LOG$ tables, and may cause fragmentation issues.

Migrating AUDIT tables from the SYSTEM tablespace to a different tablespace allows you more control over the size of your DB instance's audit tables. You can migrate the tables yourself, or if you're running Oracle 11g R2 on your RDS instance, you can use the DBMS_AUDIT_MGMT package.

To migrate the table manually, first, create a new tablespace using a command similar to the following:

CREATE TABLESPACE ;

Note: Creating a tablespace with AUTOEXTENSIBLE segment space management (enabled by default for new tablespaces) enables you to easily resize the table later.

Next, migrate your AUD$ table to the new tablespace by using commands similar to the following:

BEGIN
SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => '');
END;
/

Next, migrate your FGA_LOG$ table to the new tablespace by using commands similar to the following:

BEGIN
SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'BIGFILETEST');
END;
/

Note: Depending on the size of your table, this operation can take significant time to complete. If you have large or frequently accessed tables, you might also encounter deadlock errors; to resolve them, temporarily set DB_AUDIT_TRAIL to NONE, restart the database, and try the operations again. You can revert DB_AUDIT_TRAIL after the process is completed.

Audit your tables while keeping best practices in mind

In order to maintain manageable audit tables, mitigate the space consumed by the audit tables and minimize the occurrence of the ORA-01653 error by auditing the minimum set of pertinent actions and archiving and purging audit trail records often.

Turn AUTOEXTEND on for the SYSTEM tablespace

For DB instances running Oracle, AUTOEXTEND for the SYSTEM tablespace is set to ON by default. If you turn it off manually, it can affect your database's ability to provision additional space for the SYSTEM tablespace. You can enable this setting for the SYSTEM tablespace by using the ALTER TABLESPACE command:

ALTER TABLESPACE SYSTEM AUTOEXTEND ON;

For more information, see Oracle's documentation at Changing Data File Size.

Set MAXSIZE for the SYSTEM tablespace to a larger value

If the SYSTEM tablespace settings for MAXBYTES or MAXBLOCKS are set too low, set MAXSIZE for the SYSTEM tablespace to a larger value.

ALTER TABLESPACE SYSTEM AUTOEXTEND ON MAXSIZE UNLIMITED;

RDS, Oracle, tablespace, ora-01653


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2017-01-13