Unable to extend

Applies to:

Oracle Server - Enterprise Edition - Version: 7.0.16.0 to 11.1.0.7
Information in this document applies to any platform.

Purpose

This document can be used to diagnose and resolve space management errors of the type "unable to extend ... by %s in tablespace <name>"  where the 'by %s' extent size is the number of DB blocks.

EXAMPLE ERRORS

ORA-1650: unable to extend rollback segment %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a rollback segment in the tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1651: unable to extend save undo segment by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for saving undo entries for the indicated offline tablespace.
  Action: Check the storage parameters for the SYSTEM tablespace. The tablespace needs to be brought back online so the undo can be applied.

ORA-1652: unable to extend temp segment by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1653: unable to extend table %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1654: unable to extend index %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1655: unable to extend cluster %s.%s by %s for tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a cluster segment in tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


ORA-1683: unable to extend index %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1688: unable to extend table %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for table segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1691: unable to extend lob segment %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1692: unable to extend lob segment %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


ORA-3233: unable to extend table %s.%s subpartition %s by %s in tablespace %s
  Cause: Failed to allocate an extent for table subpartition segment in tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3234: unable to extend index %s.%s subpartition %s by %s in tablespace %s
  Cause: Failed to allocate an extent for index subpartition segment in tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3238: unable to extend LOB segment %s.%s subpartition %s by %s in tablespace %s
   Cause: An attempt was made to allocate an extent for LOB subpartition segment in tablespace, but the extent could not be allocated because there is not enough space in the tablespace indicated.
   Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Troubleshooting Details

An "unable to extend" error is raised when there is insufficient contiguous space available to extend a segment.

I. Information needed to resolve UNABLE TO EXTEND errors

In order to address UNABLE TO EXTEND errors the following information is needed:


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

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '<tablespace name>';

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

Please note that if the tablespace you are concerned with is of type TEMPORARY, then please refer to Note 188610.1.

If this query is done immediately after the failure, it will show that the largest contiguous space in the tablespace is smaller than the next extent  the object was trying to allocate.


2. Determine NEXT_EXTENT size

SELECT NEXT_EXTENT, PCT_INCREASE FROM DBA_SEGMENTS WHERE SEGMENT_NAME = <segment name> AND SEGMENT_TYPE = <segment type> AND OWNER = <owner>

<segment type> is usually stated in the error message

Possible Segment Types:

   CLUSTER
   INDEX
   INDEX PARTITION
   LOB PARTITION
   LOBINDEX
   LOBSEGMENT
   NESTED TABLE
   ROLLBACK
   TABLE
   TABLE PARTITION
   TYPE2 UNDO
   TYPE2 UNDO (ORA-1651)

<segment name> name is 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 = '<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)

EXAMPLE

next_extent = 512000
pct_increase = 50

next extent size = 512000 * (1 + (50/100)) = 512000 * 1.5 = 768000

NOTES:

ORA-01650 Rollback Segment

     pct_increase is only needed for early versions of Oracle, by  default in later versions pct_increase for a rollback segment is 0.

ORA-01652 Temporary Segment

    Temporary segments take the default storage clause of the tablespace  in which they are created.

     If this error is caused by a query, then try and ensure that the query  is tuned to perform its sorts as efficiently as possible.

    To find the owner of a sort, please refer to Note 1069041.6.


4) 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(s) for the tablespace have insufficient space then
         Add a Datafile or Defragment the Tablespace are your only options ... and this file must be added to a new volume with sufficient space

II. Possible Solutions

There are several options for resolving UNABLE TO EXTEND errors

A. Manually Coalesce Adjacent Free Extents

ALTER TABLESPACE <tablespace name> COALESCE;

The extents must be adjacent to each other for this to work.

B. Modify one or more datafiles in the tablespace to use AUTOEXTEND

ALTER DATABASE DATAFILE '<full path and name>' AUTOEXTEND ON MAXSIZE <integer> <k | m | g | t | p | e>;

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

C. Add a Datafile

ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' SIZE <integer> <k | m | g | t | p | e>;

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 <SEGMENT TYPE> <segment_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);

For non-temporary and partitioned segments:

ALTER TABLE <table_name> MODIFY PARTITION <partition_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);

For a temporary segment problem: 

ALTER TABLESPACE <tablespace name> DEFAULT STORAGE (initial <integer> <k | m | g | t | p | e> next <integer> <k | m | g | t | p | e> pctincrease <integer>);

E. Resize the Datafile

ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k | m | g | t | p | e>;