ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s

ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s

Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE



Diagnosis the problem
========================



1. Determine the largest contiguous space available for the tablespace with the error

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '';

The above query returns the largest available contiguous chunk of space.


2. Determine NEXT_EXTENT size

SELECT NEXT_EXTENT, PCT_INCREASE FROM DBA_SEGMENTS WHERE SEGMENT_NAME = AND SEGMENT_TYPE = AND OWNER =

is usually stated in the error message


3. Compute the NEXT EXTENT SIZE if the segment resides in a dictionary managed tablespace and has a PCT_INCREASE >0

SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '';

Use the "next_extent" size with "pct_increase" (from #2 above) in the following formula to determine the size of extent that is trying to be allocated.

extent size = next_extent * (1 + (pct_increase/100)



4) Determine if the tablespace containing the object is AUTOEXTENSIBLE and has reached MAXSIZ

For Data Files

SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name=' ';

For Temp Files

SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name=' ';


5) Determine which solution best fits the conditions

If the NEXT EXTENT size (step 2 or 3 above) is larger than the largest contiguous chunk of free space then
Manually Coalesce Adjacent Free Extents may be an option
If after coalescing ... there is still insufficient contiguous space ... then one of the other options must be chosen

If the volumes on which the datafile/tempfile(s) for the tablespace have insufficient space then
Add a Datafile/Tempfile or Defragment the Tablespace are your only options ... and this file must be added to a new volume with sufficient space

If the tablespace is AUTOEXTENSIBLE and at MAXSIZE ... either raise max size (after verifying that the volume has space) or Add a Datafile/Tempfile or Defragment the Tablespace



===========================
Possible solutions
=============================

A. Manually Coalesce Adjacent Free Extents
ALTER TABLESPACE COALESCE;

B. Modify one or more datafiles/tempfiles in the tablespace to use AUTOEXTEND
ALTER DATABASE DATAFILE|TEMPFILE '' AUTOEXTEND ON MAXSIZE ;

NOTE: It is strongly recommended that MAXSIZE be specified to try to prevent the datafile/tempfile from consuming all available space on the volume


C. Add a Datafile/Tempfile
ALTER TABLESPACE ADD DATAFILE|TEMPFILE '' SIZE ;


D. Lower "next_extent" and/or "pct_increase" size if segment is in a Dictionary Managed Tablespace
For non-temporary and non-partitioned segments:

ALTER STORAGE ( next pctincrease );



E. Resize the Datafile/Tempfile
ALTER DATABASE DATAFILE|TEMPFILE '' RESIZE ;


F. Defragment the Tablespace