When connecting with sqlplus user/password@service you may see a stack similar to:

• ORA-00604: error occurred at recursive SQL level 1
• ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
• ORA-02002: error while writing to audit trail

ORA-00604 indicates a failure in an internal (recursive) SQL call. ORA-01653 shows that the SYSTEM tablespace cannot extend the SYS.AUD$ segment (the audit trail), typically becuase the tablespace has run out of space.

Connect as SYSDBA

sqlplus / as sysdba

Assess space usage

Check allocated and free space for each tablespace, with a focus on SYSTEM:

-- Allocated space (MB) per tablespace
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024) AS allocated_mb
FROM   dba_data_files
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

-- Free space (MB) per tablespace
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024) AS free_mb
FROM   dba_free_space
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

Inspect the SYSTEM datafiles and their sizes:

SELECT file_id,
       file_name,
       ROUND(bytes/1024/1024) AS size_mb,
       autoextensible,
       increment_by
FROM   dba_data_files
WHERE  tablespace_name = 'SYSTEM'
ORDER  BY file_id;

Remediation options

One or more of the following actions will resolve the space pressure in SYSTEM.

A) Grow an existing SYSTEM datafile

If you have an existing file that can be enlarged, either resize it or enable autoextend.

-- Resize a specific SYSTEM datafile (adjust path and size)
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/system01.dbf' RESIZE 5600M;

-- Prefer enabling autoextend with a reasonable increment and cap
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/system01.dbf'
  AUTOEXTEND ON NEXT 256M MAXSIZE 8192M;

B) Add a new SYSTEM datafile

If resizing is not possible, add another file to the SYSTEM tablespace. For OMF/ASM, omit the file path.

-- File-system example (adjust directory and size)
ALTER TABLESPACE SYSTEM
  ADD DATAFILE '/u01/oradata/ORCL/system02.dbf' SIZE 2048M
  AUTOEXTEND ON NEXT 256M MAXSIZE 8192M;

C) Move non-dictionary objects out of SYSTEM

User objects should not live in SYSTEM. Identify and relocate anything not owned by SYS/SYSTEM.

-- List non-SYS/SYSTEM tables stored in SYSTEM
SELECT owner, table_name
FROM   dba_tables
WHERE  tablespace_name = 'SYSTEM'
AND    owner NOT IN ('SYS','SYSTEM')
ORDER  BY owner, table_name;

Generate and run MOVE commands to a dedicated applicasion tablespace (replace APP_TS with your target):

-- Generate MOVE statements
SELECT 'ALTER TABLE ' || owner || '.' || table_name ||
       ' MOVE TABLESPACE APP_TS;' AS ddl
FROM   dba_tables
WHERE  tablespace_name = 'SYSTEM'
AND    owner NOT IN ('SYS','SYSTEM');

After moving tables, rebuild their indexes in the new tablespace as needed.

Optional: Disable auditing and clean up SYS.AUD$

In Oracle 11g, standard auditnig is enabled by default. If auditing too the database is not required, you can disable it and purge the acccumulated audit rows. Changing AUDIT_TRAIL requires a restart.

1. Disable auditing to the database (SPFILE-scoped):

ALTER SYSTEM SET audit_trail = NONE SCOPE = SPFILE;

2. Restart the instance:

SHUTDOWN IMMEDIATE;
STARTUP;

3. Truncate the audit trail table:

TRUNCATE TABLE sys.aud$;

If you observe ORA-01654: unable to extend index on audit-related indexes in the alert log, free space first using options A/B/C above, then retry the cleanup.

Validation

Re-run the space queries and attempt a normal user login:

-- Verify free space in SYSTEM grew
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS free_mb
FROM   dba_free_space
WHERE  tablespace_name = 'SYSTEM'
GROUP  BY tablespace_name;

sqlplus user/password@service