ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 4096 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 4096 in tablespace SYSTEM
1. Logon to the Oracle database as a sysdba user.
2. Validate that the system tablespace is out of space.
set pagesize 500
select a.tablespace_name,
(select sum(b.bytes)/1024/1024/1024 from dba_data_files b where a.tablespace_name=b.tablespace_name) as GB_TOT,
(select sum(c.bytes)/1024/1024/1024 from dba_free_space c where a.tablespace_name=c.tablespace_name) as GB_AVAIL,
100*(select sum(c.bytes) from dba_free_space c where a.tablespace_name=c.tablespace_name)/(select sum(b.bytes) from dba_data_files b where a.tablespace_name=b.tablespace_name) as PERC_FREE
from dba_tablespaces a where tablespace_name=’SYSTEM’;
TABLESPACE_NAME GB_TOT GB_AVAIL PERC_FREE
—————————— ———- ———- ———-
SYSTEM 10.27125 .01766357 .024375
SQL>
3. The query above showes that the system tablespace is out of space. The quickest fix is to add additional space to the SYSTEM tablespace, additionally we
can address the amount of data stored in the AUD$ tables referenced in the above error message. This alternative is address in another article.
4. To add additional space to the system tablespace, find the current location of SYSTEM data files on your system with the following statement.
SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name=’SYSTEM’;
FILE_NAME BYTES/1024/1024
——————————— —————
/u01/oradata/UCOP/SYSTEM01.DBF 10000
SQL>
5. Verify that you have enough space available on your drives and add an additional datafile;
alter tablespace SYSTEM add datafile ‘/u01/oradata/UCOP/SYSTEM02.DBF’ size 10000m;
SQL> alter tablespace SYSTEM add datafile ‘/u01/oradata/UCOP/SYSTEM02.DBF’ size 10000m;
Tablespace altered.
SQL>
6. Validate that you know have available free space in the SYSTEM tablespace and re-execute the statement which caused your initial error.
set pagesize 500
select a.tablespace_name,
(select sum(b.bytes)/1024/1024/1024 from dba_data_files b where a.tablespace_name=b.tablespace_name) as GB_TOT,
(select sum(c.bytes)/1024/1024/1024 from dba_free_space c where a.tablespace_name=c.tablespace_name) as GB_AVAIL,
100*(select sum(c.bytes) from dba_free_space c where a.tablespace_name=c.tablespace_name)/(select sum(b.bytes) from dba_data_files b where a.tablespace_name=b.tablespace_name) as PERC_FREE
from dba_tablespaces a where tablespace_name=’SYSTEM’;
TABLESPACE_NAME GB_TOT GB_AVAIL PERC_FREE
—————————— ———- ———- ———-
SYSTEM 19.53125 9.1766357 49.184375
SQL>
7. This completes resolving Error ORA-01653
Larry Catt