Oracle 11g AUD$ Table Maintenance After ORA-01653: Unable to Extend Table SYS.AUD$

Oracle 11g DBA Guide: Diagnose, Move, Monitor, and Purge the SYS.AUD$ Audit Table

Oracle database administrators occasionally encounter a deceptively simple error:

ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM

At first glance, this appears to be nothing more than a tablespace-space problem. However, when the affected table is SYS.AUD$, the situation deserves more attention.

The AUD$ table is used by traditional Oracle auditing in Oracle 11g. As audit records accumulate, the table can grow significantly. If it remains in the SYSTEM tablespace and eventually cannot allocate another extent, the database can experience serious operational problems.

In some circumstances, the issue can even affect new database connections because Oracle needs to write audit information when users connect.

The immediate solution may be to add space to the SYSTEM tablespace, but that only treats the symptom. A better long-term solution is to:

  • Investigate the size and configuration of SYS.AUD$
  • Check the table's extent/storage configuration
  • Determine how quickly audit data is growing
  • Move audit data out of SYSTEM
  • Configure appropriate audit-table maintenance
  • Define an audit-data retention policy
  • Configure automated audit cleanup
  • Monitor the cleanup jobs

This guide walks through the process for Oracle Database 11g.

Important: The procedures in this article are specifically aimed at the traditional audit architecture used in Oracle 11g. Oracle 12c and later introduced Unified Auditing, so do not blindly apply an Oracle 11g AUD$ maintenance procedure to a different Oracle release.

1. Understanding ORA-01653

The error:

ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM

means Oracle attempted to allocate another extent for SYS.AUD$, but could not obtain the required space.

One common cause is that the table's NEXT extent requirement is larger than the available contiguous space in the tablespace.

The important point is that the problem isn't necessarily that the entire tablespace has absolutely no free space.

The database may have free space that cannot satisfy the extent allocation requirement.

For example, a table might require a large next extent while the available free space is fragmented into smaller pieces.

2. Why SYS.AUD$ Can Become a Problem

SYS.AUD$ contains traditional database audit records.

Depending on the auditing configuration, workload, number of users, login frequency, application activity, and retention policy, this table can grow continuously.

A busy production database can therefore accumulate millions of audit records.

The dangerous part is that AUD$ has historically been associated with the SYSTEM tablespace.

Allowing application-generated audit information to consume large amounts of the SYSTEM tablespace is not a good long-term maintenance strategy.

A much better approach is to separate audit storage from the core SYSTEM tablespace and establish an appropriate cleanup policy.

3. Immediate Response to ORA-01653

If the database is currently experiencing the problem, the first priority is to make enough space available to allow Oracle to continue operating.

Check the SYSTEM datafiles:

SET LINESIZE 200
COLUMN file_name FORMAT A80

SELECT
    file_id,
    file_name,
    bytes / 1024 / 1024 AS size_mb,
    autoextensible,
    maxbytes / 1024 / 1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM'
ORDER BY file_id;

Check free space:

SET LINESIZE 200

SELECT
    tablespace_name,
    SUM(bytes) / 1024 / 1024 AS free_mb
FROM dba_free_space
WHERE tablespace_name = 'SYSTEM'
GROUP BY tablespace_name;

You can also inspect the largest segments in SYSTEM:

SET LINESIZE 200
COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A35
COLUMN segment_type FORMAT A20

SELECT
    owner,
    segment_name,
    segment_type,
    bytes / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY bytes DESC;

If SYS.AUD$ is consuming a significant percentage of the tablespace, it should be investigated immediately.

Adding space to SYSTEM may relieve the immediate allocation failure, but it should not be considered the complete solution.

4. Document the Current AUD$ Configuration

Before making changes, document the existing configuration.

This is particularly important on production systems.

Start by checking the segment information.

SET LINESIZE 300

COLUMN owner FORMAT A10
COLUMN segment_name FORMAT A15
COLUMN segment_type FORMAT A15
COLUMN segment_subtype FORMAT A15
COLUMN tablespace_name FORMAT A20

SELECT
    owner,
    segment_name,
    segment_type,
    segment_subtype,
    tablespace_name,
    bytes / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE segment_name IN ('AUD$', 'FGA_LOG$')
ORDER BY segment_name;

This tells us where the audit tables are physically stored.

For example, you may see:

OWNER      SEGMENT_NAME   SEGMENT_TYPE   TABLESPACE_NAME   SIZE_MB
---------- -------------- -------------- ----------------- --------
SYS        AUD$           TABLE           SYSTEM            21504
SYS        FGA_LOG$       TABLE           SYSTEM                0

The important observation is:

SYS.AUD$ -> SYSTEM

If AUD$ is consuming many gigabytes of SYSTEM, moving it should be considered.

5. Check AUD$ Statistics

Next, check when the table was last analyzed and the number of rows recorded in the data dictionary statistics.

SET LINESIZE 200

COLUMN owner FORMAT A20
COLUMN table_name FORMAT A20

SELECT
    last_analyzed,
    owner,
    table_name,
    num_rows
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Example:

LAST_ANALYZED   OWNER   TABLE_NAME   NUM_ROWS
--------------  ------  -----------  ----------
01-DEC-18       SYS     AUD$         10704439

Keep in mind that NUM_ROWS comes from optimizer statistics and may not represent the exact current row count.

For an exact count:

SELECT COUNT(*) AS audit_rows
FROM SYS.AUD$;

And for Fine-Grained Auditing:

SELECT COUNT(*) AS fga_rows
FROM SYS.FGA_LOG$;

On a large audit table, COUNT(*) can require substantial work, so consider the impact before running it on a busy production database.

6. Check AUD$ Index and LOB Information

Oracle 11g AUD$ can contain LOB-related segments associated with columns such as SQLTEXT and SQLBIND.

Check the indexes associated with the audit tables:

SET LINESIZE 200

COLUMN owner FORMAT A10
COLUMN index_name FORMAT A40
COLUMN index_type FORMAT A20

SELECT
    owner,
    index_name,
    index_type,
    last_analyzed
FROM dba_indexes
WHERE table_name IN ('AUD$', 'FGA_LOG$')
  AND owner = 'SYS'
ORDER BY table_name, index_name;

You can also identify the LOB segments:

SET LINESIZE 200

COLUMN table_name FORMAT A15
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A40

SELECT
    b.table_name,
    b.column_name,
    a.segment_name,
    a.bytes / 1024 / 1024 / 1024 AS size_gb
FROM dba_segments a
JOIN dba_lobs b
  ON a.owner = b.owner
 AND a.segment_name = b.segment_name
WHERE b.table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY b.table_name, b.column_name;

This helps identify whether the LOB segments are contributing materially to the audit footprint.

7. Check Which Tablespace Contains the Audit Tables

A simple query can confirm the tablespace assignment:

SET LINESIZE 200

COLUMN table_name FORMAT A20
COLUMN tablespace_name FORMAT A30

SELECT
    table_name,
    tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Typical output might look like:

TABLE_NAME   TABLESPACE_NAME
------------ ----------------
AUD$         SYSTEM
FGA_LOG$     SYSTEM

If AUD$ is consuming a large amount of SYSTEM, this is an important finding.

8. Check the Current Audit Management Configuration

Oracle provides the DBMS_AUDIT_MGMT package for managing audit trails.

First, inspect the current configuration:

SET PAGESIZE 150
SET LINESIZE 200

COLUMN parameter_name FORMAT A35
COLUMN parameter_value FORMAT A25
COLUMN audit_trail FORMAT A30

SELECT
    parameter_name,
    parameter_value,
    audit_trail
FROM dba_audit_mgmt_config_params
ORDER BY audit_trail, parameter_name;

This provides information such as:

PARAMETER_NAME
PARAMETER_VALUE
AUDIT_TRAIL

Pay particular attention to:

  • DB AUDIT TABLESPACE

  • DB AUDIT CLEAN BATCH SIZE

  • OS audit configuration

  • XML audit configuration

9. Check the Last Archive Timestamp

Before creating a purge policy, determine whether an archive timestamp has already been configured.

SELECT *
FROM dba_audit_mgmt_last_arch_ts;

If no rows are returned:

no rows selected

there may not yet be a last archive timestamp configured for the audit trail.

You can also determine the oldest audit record currently present:

SELECT MIN(ntimestamp#) AS oldest_audit_record
FROM SYS.AUD$;

This is useful for determining how much historical audit data exists.

For example:

OLDEST_AUDIT_RECORD
------------------------------
28-JAN-19 12.20.33.754071 PM

10. Check the Current NEXT Extent Setting

The original problem may be related to the size of the next extent Oracle is attempting to allocate.

Check the storage settings for AUD$:

SELECT
    owner,
    table_name,
    next_extent,
    pct_increase,
    max_extents
FROM dba_tables
WHERE owner = 'SYS'
  AND table_name = 'AUD$';

Depending on the Oracle release and table configuration, some columns may not be populated or may have different semantics.

You can also inspect the segment directly:

SELECT
    owner,
    segment_name,
    bytes / 1024 / 1024 AS size_mb,
    extents
FROM dba_segments
WHERE owner = 'SYS'
  AND segment_name = 'AUD$';

The goal is to understand how large the segment has become and whether its allocation characteristics are appropriate.

11. Reduce an Excessive NEXT Extent

If the existing NEXT extent is unnecessarily large, it can be adjusted.

For example:

ALTER TABLE SYS.AUD$
STORAGE (NEXT 50M);

The original maintenance procedure uses a value in the range of approximately 50–100 MB as an example.

However, do not blindly use 50 MB or 100 MB in every production environment.

The appropriate extent size depends on:

  • Audit volume
  • Growth rate
  • Tablespace size
  • Storage architecture
  • Available free space
  • Database workload
  • Retention period

A very small extent may result in excessive extent allocations, while an unnecessarily large extent can make space allocation more difficult.

12. Create a Dedicated Audit Tablespace

Rather than keeping audit data in SYSTEM, consider creating a dedicated tablespace.

For example:

CREATE TABLESPACE AUDIT_TS
DATAFILE '/u01/oradata/DBNAME/audit_ts01.dbf'
SIZE 5G
AUTOEXTEND ON
NEXT 500M
MAXSIZE 50G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Important: Replace the datafile path, database name, initial size, growth increment, and maximum size with values appropriate for your environment.

Before creating the tablespace, verify your storage layout and available disk capacity.

Check existing datafiles:

SET LINESIZE 200

COLUMN file_name FORMAT A80

SELECT
    tablespace_name,
    file_name,
    bytes / 1024 / 1024 AS size_mb,
    autoextensible,
    maxbytes / 1024 / 1024 AS max_size_mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;

13. Move AUD$ Out of SYSTEM

Oracle provides DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION for relocating the database audit trail.

For the standard audit trail:

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

For Fine-Grained Auditing:

BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
        audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
        audit_trail_location_value => 'AUDIT_TS'
    );
END;
/

If your environment uses SYSAUX as the intended destination, replace:

AUDIT_TS

with:

SYSAUX

The key concept is to move the audit trail away from SYSTEM and into a tablespace designed and sized for audit data.

14. Verify the AUD$ Tablespace After the Move

After the operation completes, verify the new location.

SET LINESIZE 200

COLUMN owner FORMAT A10
COLUMN segment_name FORMAT A15
COLUMN tablespace_name FORMAT A20

SELECT
    owner,
    segment_name,
    segment_type,
    tablespace_name,
    bytes / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE segment_name = 'AUD$';

Also check the table definition:

SELECT
    owner,
    table_name,
    tablespace_name
FROM dba_tables
WHERE owner = 'SYS'
  AND table_name IN ('AUD$', 'FGA_LOG$');

The expected result is that the audit table is no longer consuming the SYSTEM tablespace.

15. Recheck SYSTEM Tablespace Usage

After moving the audit table, check SYSTEM again.

SELECT
    tablespace_name,
    SUM(bytes) / 1024 / 1024 AS free_mb
FROM dba_free_space
WHERE tablespace_name = 'SYSTEM'
GROUP BY tablespace_name;

You can also check the largest remaining segments:

SELECT
    owner,
    segment_name,
    segment_type,
    bytes / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY bytes DESC;

This provides a much clearer picture of what is consuming the core system tablespace.

16. Establish an Audit Retention Policy

Moving the audit table does not solve unlimited growth.

The next question should be:

How long does the organization actually need to retain audit records?

For example:

  • 30 days
  • 90 days
  • 180 days
  • 1 year

Multiple years

The correct retention period depends on:


  • Security requirements
  • Regulatory requirements
  • Internal policies
  • Application requirements
  • Audit requirements
  • Legal requirements

Do not automatically delete audit records simply because they are old.

Establish and document the retention requirement first.

For this example, we will use 365 days.

17. Initialize Audit Cleanup

Oracle provides DBMS_AUDIT_MGMT.INIT_CLEANUP.

Initialize cleanup with a 24-hour interval:

BEGIN
    DBMS_AUDIT_MGMT.INIT_CLEANUP(
        AUDIT_TRAIL_TYPE      => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        DEFAULT_CLEANUP_INTERVAL => 24
    );
END;
/

Verify the configuration:

SET PAGESIZE 150
SET LINESIZE 200

COLUMN parameter_name FORMAT A35
COLUMN parameter_value FORMAT A25
COLUMN audit_trail FORMAT A30

SELECT
    parameter_name,
    parameter_value,
    audit_trail
FROM dba_audit_mgmt_config_params
ORDER BY audit_trail, parameter_name;

18. Create a Procedure to Set the Last Archive Timestamp

For a one-year retention period, create a procedure that establishes the cutoff timestamp.

CREATE OR REPLACE PROCEDURE AUD_SET_LAST_ARCH_TS
AS
    retention NUMBER;
BEGIN
    retention := 365; -- retention in days

    SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
        AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        LAST_ARCHIVE_TIME => SYSTIMESTAMP - retention
    );
END;
/

The important part is:

SYSTIMESTAMP - retention

With:

retention := 365;

the archive timestamp is set to approximately one year before the current timestamp.

19. Verify the Procedure

Check that the procedure was created successfully:

SELECT
    owner,
    object_name,
    object_type,
    status
FROM dba_objects
WHERE object_name = 'AUD_SET_LAST_ARCH_TS';

You want:

STATUS
------
VALID

If it is invalid, inspect compilation errors:

SHOW ERRORS PROCEDURE AUD_SET_LAST_ARCH_TS;

20. Create a Scheduler Job to Update the Archive Timestamp

The timestamp needs to move forward over time.

Create a scheduler job:

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_SET_LAST_ARCH_TS',
        schedule_name   => 'SYS.MAINTENANCE_WINDOW_GROUP',
        job_class       => 'DEFAULT_JOB_CLASS',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN AUD_SET_LAST_ARCH_TS(); END;',
        comments        => 'Job to maintain the audit archive timestamp'
    );

    SYS.DBMS_SCHEDULER.ENABLE(
        name => 'JOB_SET_LAST_ARCH_TS'
    );
END;
/

This job executes the procedure that updates the last archive timestamp.

21. Create the Audit Purge Job

Now create the actual audit cleanup job.

BEGIN
    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
        audit_trail_type          => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        audit_trail_purge_interval => 24,
        audit_trail_purge_name    => 'JOB_PURGE_AUDIT_TRAIL',
        use_last_arch_timestamp   => TRUE
    );
END;
/

The important parameters are:

AUDIT_TRAIL_ALL

to cover the configured audit trails,

audit_trail_purge_interval => 24

to run cleanup every 24 hours,

and:

use_last_arch_timestamp => TRUE

so the purge operation uses the archive timestamp as its retention boundary.

22. Enable the Purge Job

Create the job and then explicitly enable it:

BEGIN
    DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
        audit_trail_purge_name   => 'JOB_PURGE_AUDIT_TRAIL',
        audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE
    );
END;
/

At this point, the database has an automated mechanism for removing audit records beyond the defined retention boundary.

23. Check the Audit Cleanup Job

Verify the cleanup job:

SET LINESIZE 200

COLUMN job_name FORMAT A35
COLUMN job_frequency FORMAT A30

SELECT *
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

Look for:

JOB_PURGE_AUDIT_TRAIL

and confirm that it is enabled and configured for the expected interval.

24. Check the Scheduler Job

You can also inspect Oracle Scheduler directly:

SET LINESIZE 200

COLUMN job_name FORMAT A35
COLUMN enabled FORMAT A10
COLUMN state FORMAT A15
COLUMN last_start_date FORMAT A35
COLUMN next_run_date FORMAT A35

SELECT
    job_name,
    enabled,
    state,
    last_start_date,
    next_run_date
FROM dba_scheduler_jobs
WHERE job_name IN (
    'JOB_SET_LAST_ARCH_TS',
    'JOB_PURGE_AUDIT_TRAIL'
)
ORDER BY job_name;

This is useful for confirming whether the scheduler jobs are enabled and when they last ran or are expected to run next.

25. Check Audit Cleanup History

Oracle also provides a view showing cleanup activity:

SET LINESIZE 200

SELECT *
FROM DBA_AUDIT_MGMT_CLEAN_EVENTS
ORDER BY event_timestamp DESC;

This helps determine whether audit cleanup operations are actually taking place.

For a more focused view:

SELECT
    event_timestamp,
    audit_trail,
    delete_count,
    start_time,
    end_time
FROM DBA_AUDIT_MGMT_CLEAN_EVENTS
ORDER BY event_timestamp DESC;

Column availability can vary depending on the exact Oracle 11g release and view definition, so verify the columns in your environment:

DESC DBA_AUDIT_MGMT_CLEAN_EVENTS;

26. Monitor AUD$ Growth

After moving the table and implementing cleanup, continue monitoring it.

Check current size:

SELECT
    owner,
    segment_name,
    bytes / 1024 / 1024 AS size_mb,
    extents
FROM dba_segments
WHERE owner = 'SYS'
  AND segment_name = 'AUD$';

Check row count:

SELECT COUNT(*) AS audit_rows
FROM SYS.AUD$;

Check the oldest record:

SELECT MIN(ntimestamp#) AS oldest_record
FROM SYS.AUD$;

These three queries provide a simple ongoing health check:

  1. How much physical space is being consumed?

  2. How many audit records exist?

  3. How old is the oldest audit record?

27. Calculate Audit Growth

For capacity planning, capture the AUD$ size periodically.

For example:

SELECT
    SYSDATE AS check_date,
    bytes / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE owner = 'SYS'
  AND segment_name = 'AUD$';

Run this daily or weekly and store the results.

Over time, you can determine:

Daily growth
Weekly growth
Monthly growth
Peak growth

This information is extremely useful when sizing the dedicated audit tablespace.

28. Check Tablespace Free Space

For the audit tablespace:

SELECT
    tablespace_name,
    SUM(bytes) / 1024 / 1024 AS free_mb
FROM dba_free_space
WHERE tablespace_name = 'AUDIT_TS'
GROUP BY tablespace_name;

You can compare that with total allocated space:

SELECT
    tablespace_name,
    SUM(bytes) / 1024 / 1024 AS allocated_mb
FROM dba_data_files
WHERE tablespace_name = 'AUDIT_TS'
GROUP BY tablespace_name;

A simple percentage calculation can help with monitoring:

SELECT
    df.tablespace_name,
    df.total_mb,
    NVL(fs.free_mb, 0) AS free_mb,
    df.total_mb - NVL(fs.free_mb, 0) AS used_mb,
    ROUND(
        ((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb) * 100,
        2
    ) AS used_pct
FROM
    (
        SELECT
            tablespace_name,
            SUM(bytes) / 1024 / 1024 AS total_mb
        FROM dba_data_files
        WHERE tablespace_name = 'AUDIT_TS'
        GROUP BY tablespace_name
    ) df
LEFT JOIN
    (
        SELECT
            tablespace_name,
            SUM(bytes) / 1024 / 1024 AS free_mb
        FROM dba_free_space
        WHERE tablespace_name = 'AUDIT_TS'
        GROUP BY tablespace_name
    ) fs
ON df.tablespace_name = fs.tablespace_name;

29. Check Whether Audit Purging Is Working

A healthy cleanup configuration should show evidence of regular cleanup.

Check:

SELECT *
FROM DBA_AUDIT_MGMT_CLEAN_EVENTS
ORDER BY event_timestamp DESC;

Check the cleanup jobs:

SELECT *
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

Check the Scheduler:

SELECT
    job_name,
    enabled,
    state,
    last_start_date,
    next_run_date
FROM DBA_SCHEDULER_JOBS
WHERE job_name IN (
    'JOB_SET_LAST_ARCH_TS',
    'JOB_PURGE_AUDIT_TRAIL'
);

If the jobs are enabled but are not running successfully, investigate the Scheduler job details and alert log.

30. Verify That the Retention Boundary Is Moving

Check:

SELECT *
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

The timestamp should advance as the scheduled procedure executes.

You can also manually execute the procedure during testing:

BEGIN
    AUD_SET_LAST_ARCH_TS;
END;
/

Then verify:

SELECT *
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

Do this carefully in production because changing the archive timestamp influences which audit records qualify for cleanup.

31. Validate the Oldest Remaining Audit Record

After cleanup has run, check:

SELECT
    MIN(ntimestamp#) AS oldest_audit_record,
    MAX(ntimestamp#) AS newest_audit_record
FROM SYS.AUD$;

You should see the oldest record moving toward the configured retention boundary after successful purge cycles.

Do not expect the physical segment size to immediately shrink simply because rows were deleted.

This is an important Oracle storage concept.

32. Why Purging Rows Does Not Necessarily Shrink the Datafile

Deleting audit records does not automatically return the freed blocks to the operating system.

For example:

DELETE FROM SYS.AUD$;

would remove rows but would not necessarily reduce the size of the underlying datafile.

Similarly, an automated audit purge is intended primarily to remove obsolete records and control future growth.

If the objective is to actually reduce the physical datafile size, that is a separate storage-management operation and requires careful planning.

Do not attempt to shrink or resize database files casually on a production system.

33. Important Production Considerations

Before moving SYS.AUD$ or configuring automated deletion, consider the following.

Backup

Ensure the database is protected by a valid backup strategy.

Before significant structural maintenance, verify that the latest backup is usable.

Audit Requirements

Do not select a retention period simply because it makes the database smaller.

Confirm the retention period with:

  • Security
  • Compliance
  • Application owners
  • Internal audit
  • Legal/regulatory teams

Test First

If possible, test the procedure in a development or staging environment that resembles production.

Maintenance Window

Moving system-owned audit structures can have operational implications.

Plan the change carefully.

Tablespace Capacity

Make sure the destination tablespace has enough capacity for existing audit data plus future growth.

Monitoring

Moving AUD$ does not eliminate the need for monitoring.

It simply gives the audit trail a more appropriate storage and maintenance strategy.

34. Complete Diagnostic Script

The following script can be used as a starting point for documenting the current state.

SET LINESIZE 300
SET PAGESIZE 200

PROMPT =========================================
PROMPT AUD$ SEGMENT INFORMATION
PROMPT =========================================

SELECT
    owner,
    segment_name,
    segment_type,
    tablespace_name,
    bytes / 1024 / 1024 AS size_mb,
    extents
FROM dba_segments
WHERE segment_name IN ('AUD$', 'FGA_LOG$')
ORDER BY segment_name;


PROMPT =========================================
PROMPT AUD$ TABLE INFORMATION
PROMPT =========================================

SELECT
    owner,
    table_name,
    tablespace_name,
    last_analyzed,
    num_rows
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;


PROMPT =========================================
PROMPT AUDIT TABLE COUNTS
PROMPT =========================================

SELECT COUNT(*) AS AUD_ROWS
FROM SYS.AUD$;

SELECT COUNT(*) AS FGA_ROWS
FROM SYS.FGA_LOG$;


PROMPT =========================================
PROMPT OLDEST AUDIT RECORD
PROMPT =========================================

SELECT
    MIN(ntimestamp#) AS oldest_audit_record,
    MAX(ntimestamp#) AS newest_audit_record
FROM SYS.AUD$;


PROMPT =========================================
PROMPT AUDIT TABLESPACE CONFIGURATION
PROMPT =========================================

SELECT
    table_name,
    tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;


PROMPT =========================================
PROMPT AUDIT MANAGEMENT CONFIGURATION
PROMPT =========================================

SELECT *
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;


PROMPT =========================================
PROMPT LAST ARCHIVE TIMESTAMP
PROMPT =========================================

SELECT *
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;


PROMPT =========================================
PROMPT AUDIT CLEANUP JOBS
PROMPT =========================================

SELECT *
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;


PROMPT =========================================
PROMPT CLEANUP HISTORY
PROMPT =========================================

SELECT *
FROM DBA_AUDIT_MGMT_CLEAN_EVENTS
ORDER BY event_timestamp DESC;

35. Complete Maintenance Example

A simplified implementation could look like this.

Step 1 — Reduce an unnecessarily large NEXT extent

ALTER TABLE SYS.AUD$
STORAGE (NEXT 50M);

Step 2 — Move the standard audit trail

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

Step 3 — Move the FGA audit trail

BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
        audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
        audit_trail_location_value => 'AUDIT_TS'
    );
END;
/

Step 4 — Initialize cleanup

BEGIN
    DBMS_AUDIT_MGMT.INIT_CLEANUP(
        AUDIT_TRAIL_TYPE          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        DEFAULT_CLEANUP_INTERVAL  => 24
    );
END;
/

Step 5 — Create the retention procedure

CREATE OR REPLACE PROCEDURE AUD_SET_LAST_ARCH_TS
AS
    retention NUMBER;
BEGIN
    retention := 365;

    SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
        AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        LAST_ARCHIVE_TIME => SYSTIMESTAMP - retention
    );
END;
/

Step 6 — Create the timestamp job

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_SET_LAST_ARCH_TS',
        schedule_name   => 'SYS.MAINTENANCE_WINDOW_GROUP',
        job_class       => 'DEFAULT_JOB_CLASS',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN AUD_SET_LAST_ARCH_TS(); END;',
        comments        => 'Job to maintain audit retention timestamp'
    );

    SYS.DBMS_SCHEDULER.ENABLE(
        name => 'JOB_SET_LAST_ARCH_TS'
    );
END;
/

Step 7 — Create the purge job

BEGIN
    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
        audit_trail_type           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        audit_trail_purge_interval => 24,
        audit_trail_purge_name     => 'JOB_PURGE_AUDIT_TRAIL',
        use_last_arch_timestamp    => TRUE
    );
END;
/

Step 8 — Enable the purge job

BEGIN
    DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
        audit_trail_purge_name   => 'JOB_PURGE_AUDIT_TRAIL',
        audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE
    );
END;
/

Step 9 — Verify

SELECT *
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

SELECT *
FROM DBA_AUDIT_MGMT_CLEAN_EVENTS
ORDER BY event_timestamp DESC;

36. Troubleshooting the Cleanup Configuration

If the purge job is not behaving as expected, start with the configuration.

Check the cleanup parameters

SELECT *
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

Check the archive timestamp

SELECT *
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

Check the cleanup jobs

SELECT *
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

Check Scheduler status

SELECT
    job_name,
    enabled,
    state,
    failure_count,
    last_start_date,
    next_run_date
FROM DBA_SCHEDULER_JOBS
WHERE job_name IN (
    'JOB_SET_LAST_ARCH_TS',
    'JOB_PURGE_AUDIT_TRAIL'
);

Check Scheduler run history

SELECT
    job_name,
    status,
    actual_start_date,
    run_duration,
    error#
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name IN (
    'JOB_SET_LAST_ARCH_TS',
    'JOB_PURGE_AUDIT_TRAIL'
)
ORDER BY actual_start_date DESC;

If a job fails, inspect the ERROR# and corresponding Oracle error information.

37. A Better DBA Maintenance Strategy

The important lesson from ORA-01653 is that database maintenance should be proactive rather than reactive.

A healthy Oracle 11g audit-management strategy should include four components:

1. Separate Storage

Keep large audit structures out of SYSTEM whenever practical.

2. Appropriate Extent Configuration

Avoid unnecessarily large extent allocation requirements.

3. Defined Retention

Determine exactly how long audit records need to be retained.

4. Automated Cleanup

Use Oracle's audit-management facilities and Scheduler to continuously maintain the audit trail.

This transforms the problem from:

SYSTEM tablespace is full
        ↓
ORA-01653
        ↓
Production incident

into:

Audit records
      ↓
Dedicated audit tablespace
      ↓
Defined retention policy
      ↓
Automated purge
      ↓
Continuous monitoring

38. Final Verification Checklist

After completing the maintenance, verify each of the following.

[ ] ORA-01653 has been resolved
[ ] SYSTEM tablespace has adequate free space
[ ] SYS.AUD$ is no longer consuming unnecessary SYSTEM space
[ ] Destination audit tablespace has adequate capacity
[ ] AUD$ NEXT extent is appropriate
[ ] FGA_LOG$ location has been reviewed
[ ] Audit retention period has been documented
[ ] DBMS_AUDIT_MGMT cleanup has been initialized
[ ] Last archive timestamp is configured
[ ] Timestamp scheduler job is enabled
[ ] Audit purge job is enabled
[ ] Cleanup history shows successful executions
[ ] Scheduler history shows successful job runs
[ ] Audit table growth is being monitored
[ ] Backup and recovery requirements have been considered

Conclusion

ORA-01653: unable to extend table SYS.AUD$ should not be treated as simply another datafile-space alert.

When SYS.AUD$ grows significantly, especially when it resides in the SYSTEM tablespace, the database can become vulnerable to recurring space problems and operational issues.

The immediate response may be to add space to SYSTEM, but the long-term solution is to understand why the audit table is growing and establish proper lifecycle management.

For Oracle 11g environments, a practical approach is to:

  1. Investigate SYS.AUD$

  2. Check its size and growth

  3. Review its extent configuration

  4. Move the audit trail to an appropriate tablespace

  5. Define an audit retention policy

  6. Initialize DBMS_AUDIT_MGMT

  7. Configure a last-archive timestamp

  8. Create an automated purge job

  9. Monitor Scheduler and cleanup history

  10. Continuously monitor audit tablespace utilization

The goal is not simply to fix today's ORA-01653.

The goal is to make sure the same audit-table space problem does not become tomorrow's production outage.

Always test changes in a non-production environment first and adapt the SQL, tablespace sizing, retention period, and maintenance schedule to your specific Oracle 11g environment.





Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment