ORA-01653: unable to extend table SYS.AUD$ by 8091 in table space SYSTEM

Problem: when connecting to oracle server from any application, I receive an error

ORA-01653: unable to extend table SYS.AUD$ by 8091 in table space SYSTEM.

ORA-02002: error while writing to audit trail.

Action: when you see that message, that mean the problem is from the oracle space. That problem could be either because of the physical hard is out of space and you need to increase it or oracle is hit the maximum allowed size for it and you need to increase system table space.

To solve the second problem Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the table space indicated.

Diagnostic Steps:
—————–
1. In order to see the free space available for a particular table space, you must use the view DBA_FREE_SPACE. Within this view, each record represents one fragment of space.

SQL > Describe DBA_FREE_SPACE;

NAME                                                  Null ?            Type

——————————————– —————-  ——————————–

TABLESPACE_NAME                                                   VARCHAR2(30)

FILE_ID                                                                   NUMBER

BLOCK_ID                                                               NUMBER

BYTES                                                                    NUMBER
BLOCKS                                                                  NUMBER
RELATIVE_FNO                                                         NUMBER

SQL > Select sum(bytes) from DBA_FREE_SPACE where tablespace = ‘SYSTEM’ ;

2. The DBA_TABLES view describes the size of next extent (NEXT_EXTENT) and the percentage increase (PCT_INCREASE) for all tables in the database.
The “next_extent” size is the size of extent that is trying to be allocated (and for which you have the error).

When the extent is allocated:
next_extent = next_extent * (1 + (pct_increase/100))

Algorythm to allocate extent for segment is described in the Concept Guide

3. Look to see if any users have the tablespace in question as their temporary tablespace.
This can be checked by looking at DBA_USERS (TEMPORARY_TABLESPACE).

Possible solutions:
——————-
– Manually Coalesce Adjacent Free Extents
ALTER TABLESPACE COALESCE;
The extents must be adjacent to each other for this to work.

– Add a Datafile:
ALTER TABLESPACE ADD DATAFILE ”
SIZE ;

– Resize the Datafile:
ALTER DATABASE DATAFILE ” RESIZE ;

– Enable autoextend:
ALTER DATABASE DATAFILE ?? AUTOEXTEND ON
MAXSIZE UNLIMITED;

– Defragment the Tablespace:

– Lower “next_extent” and/or “pct_increase” size:
ALTER STORAGE ( next
pctincrease );

– If the tablespace is being used as a temporary tablespace, temporary segments may
be still holding the space.

Advertisements