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.
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
——————————————– —————- ——————————–
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).
– 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 ”
– Resize the Datafile:
ALTER DATABASE DATAFILE ” RESIZE ;
– Enable autoextend:
ALTER DATABASE DATAFILE ?? AUTOEXTEND ON
– Defragment the Tablespace:
– Lower “next_extent” and/or “pct_increase” size:
ALTER STORAGE ( next
– If the tablespace is being used as a temporary tablespace, temporary segments may
be still holding the space.