Explanation:
------------
This error does not necessarily indicate whether or not you have enough space
in the tablespace, it merely indicates that Oracle could not find a large enough area of free
contiguous space in which to fit the next extent.
Diagnostic Steps:
-----------------
1. In order to see the free space available for a particular tablespace, you must
use the view DBA_FREE_SPACE. Within this view, each record represents one
fragment of space. How the view DBA_FREE_SPACE can be used to determine
the space available in the database is described in:
[NOTE:121259.1] Using DBA_FREE_SPACE
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
Chapter : Data Blocks, Extents, and Segments - How Extents Are Allocated
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.
Partager