FOA(Fun Oracle Apps) - Learning Never Stops

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

noreply@blogger.com (Himanshu) — Thu, 3 Sep 2026 12:47:06 +0530

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:

How much physical space is being consumed?
How many audit records exist?
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:

Investigate SYS.AUD$
Check its size and growth
Review its extent configuration
Move the audit trail to an appropriate tablespace
Define an audit retention policy
Initialize DBMS_AUDIT_MGMT
Configure a last-archive timestamp
Create an automated purge job
Monitor Scheduler and cleanup history
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

Oracle Reports RWConverter Crash During REX to RDF Conversion

noreply@blogger.com (Himanshu) — Thu, 4 Jun 2026 09:29:22 +0530

Oracle Reports RWConverter Crash During REX to RDF Conversion

While working on an Oracle Reports migration task, I encountered an issue where rwconverter consistently failed while converting a report from REX to RDF format.

The error was not very descriptive. The converter crashed and generated a stack trace referencing Oracle Reports runtime libraries (librw.so) and PL/SQL parsing functions. At first glance, it appeared to be a report corruption issue or an Oracle Reports bug.

Fortunately, the fix turned out to be quite simple.

The Problem

The report conversion was being executed using:

rwconverter.sh \
userid=user/password@DB \
stype=rex \
source=myreport.rex \
dtype=rdf \
dest=myreport.rdf

Instead of completing successfully, rwconverter terminated unexpectedly and displayed a stack trace containing references to Oracle Reports internal libraries and PL/SQL processing routines.

Because the failure occurred during report parsing, initial troubleshooting focused on:

Invalid report objects
Corrupted report definitions
Invalid database packages
Oracle Reports patches
Environment configuration issues

None of these investigations revealed the root cause.

The Solution

Before executing rwconverter, set the following environment variable:

export DE_DISABLE_PLS_512=0

Then rerun the conversion:

export DE_DISABLE_PLS_512=0

rwconverter.sh \
userid=user/password@DB \
stype=rex \
source=myreport.rex \
dtype=rdf \
dest=myreport.rdf

After setting this variable, the conversion completed successfully and the RDF file was generated without errors.

What Does DE_DISABLE_PLS_512 Mean?

DE_DISABLE_PLS_512 is an Oracle Developer/Reports environment variable related to the PL/SQL engine used by Oracle Developer tools.

The variable influences how Oracle Reports handles PL/SQL processing internally. During report compilation, conversion, and validation, Oracle Reports parses embedded PL/SQL contained in:

Formula columns
Report triggers
Program units
Lexical parameters
PL/SQL libraries

In certain environments, PL/SQL processing can cause unexpected failures during report conversion. Setting:

export DE_DISABLE_PLS_512=0

ensures that the appropriate PL/SQL handling mechanism is enabled during execution, which can prevent crashes encountered while converting reports.

Although Oracle has not widely documented every internal behavior associated with this variable, it has been known within Oracle Reports troubleshooting scenarios to resolve issues related to PL/SQL parsing and report compilation.

When Should You Try This?

Consider testing this setting if:

rwconverter crashes during execution
You are converting reports from REX to RDF
Stack traces reference librw.so
The failure appears related to PL/SQL parsing
Database objects are valid but the converter still fails
Manual report recompilation is being performed as part of troubleshooting

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

ORA-01555 on Physical Standby Databases Opened in Read Only Mode

noreply@blogger.com (Himanshu) — Fri, 29 May 2026 10:47:59 +0530

ORA-01555 on Physical Standby Databases Opened in Read Only Mode

Deep Dive into Snapshot Too Old Errors in Active Data Guard Environments

Modern Oracle environments frequently use standby databases not only for disaster recovery but also for reporting and analytics. With Active Data Guard, organizations can open a physical standby database in read-only mode while redo apply continues in real time. This architecture is powerful, but it introduces a problem many DBAs do not initially expect:

ORA-01555: snapshot too old

Most administrators associate ORA-01555 with highly transactional primary databases. However, this error can also occur on standby databases even though users are not directly modifying data there.

This article explains why ORA-01555 happens on standby databases, how Oracle internally manages read consistency during redo apply, and the most effective methods to eliminate the issue in production systems.

Understanding ORA-01555

ORA-01555 occurs when Oracle cannot reconstruct an older version of a data block required for query consistency.

Oracle guarantees that a query sees data exactly as it existed when the query started. This mechanism is called read consistency.

To achieve this consistency, Oracle uses undo records. When blocks change, Oracle stores older versions of the data inside undo segments so queries can continue reading a consistent image.

The error appears when:

A query runs for a long period
Oracle needs older undo information
The required undo has already been overwritten

At that point, Oracle can no longer recreate the earlier block image, and the query fails with ORA-01555.

Why ORA-01555 Happens on a Read-Only Standby

One of the biggest misconceptions is that standby databases do not generate undo because users cannot perform DML operations.

That assumption is incorrect.

In an Active Data Guard configuration:

Redo is continuously shipped from the primary database
Managed Recovery Process (MRP) applies those changes on standby
Data blocks on standby are constantly changing internally
Oracle still requires undo information to maintain read consistency for active queries

Although the standby is open in read-only mode for users, redo apply behaves like continuous internal update activity.

As a result, reporting queries running on standby may need older versions of blocks while redo apply continues modifying them.

If the necessary undo information disappears before the query completes, Oracle raises:

ORA-01555: snapshot too old

Internal Concept Behind the Error

To fully understand the issue, consider the following example.

A reporting query begins at:

10:00 AM

At the same time:

Redo from primary continues arriving
MRP continuously applies changes
Blocks are updated internally on standby
Undo records are generated and reused

Suppose the query still requires an old version of a block at:

10:45 AM

But the undo information needed to reconstruct that block has already been overwritten due to ongoing redo apply activity.

Oracle can no longer provide a consistent image to the query, which leads to:

ORA-01555

This is why even a “read-only” standby database can experience snapshot too old errors.

Common Reasons for ORA-01555 on Standby

1. Long-Running Reporting Queries

This is the most common cause.

Large reporting jobs, BI dashboards, or ETL extraction queries may run for several hours while redo apply continuously changes underlying blocks.

The longer the query runs, the higher the chance that required undo information gets overwritten.

2. Heavy Redo Generation on Primary

Standby databases directly inherit workload pressure from the primary database.

Operations such as:

Bulk updates
Data purges
Massive ETL jobs
Index rebuilds
Batch processing

generate huge amounts of redo.

When redo apply becomes extremely active, undo segments on standby recycle more aggressively.

This significantly increases ORA-01555 probability.

3. Small Undo Tablespace

Even if undo retention is configured correctly, a small undo tablespace forces Oracle to reuse undo extents earlier than desired.

Many standby databases are configured with minimal storage because administrators assume they only serve DR purposes. Once reporting workloads begin using standby, that sizing becomes insufficient.

4. Low Undo Retention

Undo retention determines how long Oracle attempts to preserve undo data.

If retention is shorter than the runtime of reporting queries, undo may disappear before queries finish.

5. Simultaneous Reporting and Recovery Peaks

ORA-01555 commonly appears during periods where:

Reporting activity is high
Redo apply is also extremely active

For example:

Nightly reporting overlapping with ETL windows
Financial reporting during month-end loads
BI extractions during bulk imports

Diagnosing ORA-01555 on Standby

The first step is confirming that the standby database is actively applying redo while serving queries.

Check Database Role and Open Mode

SELECT database_role,
       open_mode
FROM v$database;

Typical output:

PHYSICAL STANDBY
READ ONLY WITH APPLY

This confirms Active Data Guard is enabled.

Identifying Long Running Queries

Queries with long execution times are primary candidates.

SELECT sid,
       serial#,
       sql_id,
       username,
       event,
       last_call_et
FROM v$session
WHERE status='ACTIVE'
ORDER BY last_call_et DESC;

Focus on sessions with very large LAST_CALL_ET values.

Analyzing Undo Statistics

Oracle provides undo history in V$UNDOSTAT.

SELECT begin_time,
       tuned_undoretention,
       maxquerylen,
       undoblks,
       txncount
FROM v$undostat
ORDER BY begin_time;

Important values:

Column	Meaning
TUNED_UNDORETENTION	-- Actual effective undo retention
MAXQUERYLEN	-- Longest running query duration

If:

MAXQUERYLEN > TUNED_UNDORETENTION

then ORA-01555 risk is extremely high.

Checking Undo Tablespace Size

SELECT tablespace_name,
       SUM(bytes)/1024/1024 MB
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%'
GROUP BY tablespace_name;

Small undo tablespaces are often the root cause in standby reporting systems.

Reviewing Alert Logs

Alert logs help correlate ORA-01555 with workload spikes.

Search for:

ORA-01555

Then compare timestamps with:

ETL execution
Batch jobs
Redo spikes
Reporting schedules

Best Solutions for ORA-01555 on Standby

Increase Undo Retention

One of the most effective fixes is increasing undo retention.

Check current value:

SHOW PARAMETER undo_retention;

Increase retention:

ALTER SYSTEM SET undo_retention=14400;

This preserves undo for four hours.

However, retention alone is not sufficient unless adequate undo space exists.

Expand Undo Tablespace

A small undo tablespace causes Oracle to overwrite undo regardless of retention settings.

Adding additional space is often the real fix.

Example:

ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/u01/oradata/UNDOTBS02.dbf'
SIZE 20G AUTOEXTEND ON;

Also consider enabling autoextend:

ALTER DATABASE DATAFILE
'/u01/oradata/UNDOTBS01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

Optimize Reporting Queries

Long-running queries increase ORA-01555 exposure.

Performance tuning can dramatically reduce failures.

Key improvements include:

Using indexes efficiently
Eliminating unnecessary full table scans
Partition pruning
Reducing sorting overhead
Improving join conditions
Avoiding Cartesian joins

Reducing query runtime reduces undo dependency duration.

Reduce Redo Apply During Reporting Windows

Some organizations temporarily pause redo apply during critical reporting periods.

Stop apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Restart apply afterward:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

This approach reduces internal block churn while reports execute.

However, it also increases standby lag and should be used carefully.

Separate Reporting from Disaster Recovery

Large enterprise environments often deploy:

One standby for disaster recovery
Another standby dedicated for reporting

This isolates reporting workloads from recovery activity and significantly improves stability.

Monitor Undo Proactively

Many ORA-01555 incidents can be avoided through monitoring.

Recommended query:

SELECT tuned_undoretention,
       maxquerylen
FROM v$undostat;

If maximum query length consistently approaches undo retention values, action should be taken before failures occur.

Real Production Scenario

A financial institution deployed Oracle 19c Active Data Guard for reporting.

The standby database was configured with:

8 GB undo tablespace
900-second undo retention

Nightly ETL jobs on primary generated massive redo volumes between 1 AM and 3 AM.

At the same time, reporting teams executed large BI reports that ran for nearly two hours.

During peak windows, reports failed repeatedly with:

ORA-01555: snapshot too old

Investigation revealed that redo apply activity recycled undo much faster than reporting queries could complete.

The DBA team implemented the following changes:

Increased undo retention to four hours
Expanded undo tablespace from 8 GB to 64 GB
Tuned reporting SQL
Rescheduled ETL overlap

After these adjustments:

ORA-01555 incidents disappeared
Reporting stabilized
Standby lag remained acceptable

Key Takeaways

ORA-01555 on standby databases is not unusual in Active Data Guard environments.

Even though users cannot modify data directly on standby, redo apply continuously changes blocks internally, and Oracle still relies on undo for consistent reads.

The most common causes include:

Long-running queries
Heavy redo apply
Insufficient undo retention
Small undo tablespaces

The most effective solutions are:

Increase undo retention
Expand undo tablespace
Tune reporting SQL
Reduce overlap between heavy redo generation and reporting workloads

Understanding how Oracle maintains read consistency during redo apply is essential for designing stable and scalable standby reporting environments.

When properly sized and monitored, Active Data Guard can support large reporting workloads without ORA-01555 interruptions.

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