Tag Archives: Ora-01653

audit, error, Oracle

Resolving Oracle Error ORA-01653 by removing audit records

Leave a comment

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 19.53125 9.1766357 49.184375

SQL>

3. The query above showes that the system tablespace is out of space. In this article we will purge records from the SYS.AUD$ table to address this error, additionally we
can add additional storage space to address the above error message. This alternative is addressed in another article.

4. Verify that AUD$ tables is indeed consuming a considerable amount of space.

SQL> select sum(bytes)/1024/1024 from dba_segments where SEGMENT_NAME=’AUD$’;

SUM(BYTES)/1024/1024
——————–
9694

SQL>

5. Purge this records with the following statement which remove all records older then 1 day.

SQL> truncate table sys.aud$;

Table truncated.

SQL> commit;

Commit complete.

SQL>

6. Validate that the SYSTEM tablespace has increased.

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 19.1766357 98.184375

SQL>

7. This completes resolving Error ORA-01653

Larry Catt

error, Oracle

Resolving Oracle Error ORA-01653 by adding additional storage

Leave a comment

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