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

How to Diagnose and Resolve UNABLE TO EXTEND Errors
	Doc ID:	1025288.6	Type:	TROUBLESHOOTING
	Modified Date :	06-MAY-2009	Status:	PUBLISHED

In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
I. Information needed to resolve UNABLE TO EXTEND errors
II. Possible Solutions
A. Manually Coalesce Adjacent Free Extents
B. Modify one or more datafiles in the tablespace to use AUTOEXTEND
C. Add a Datafile
D. Lower "next_extent" and/or "pct_increase" size if segment is in a Dictionary Managed Tablespace
F. Defragment the Tablespace
References

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 " 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.

Last Review Date

March 25, 2008

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

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 = '';

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 = AND SEGMENT_TYPE = AND OWNER =

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)

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 = '';

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 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_temo_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(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

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

II. Possible Solutions

There are several options for resolving UNABLE TO EXTEND errors

A. Manually Coalesce Adjacent Free Extents

ALTER TABLESPACE 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 '' AUTOEXTEND ON MAXSIZE ;

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 ADD DATAFILE '' 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 );

For non-temporary and partitioned segments:

ALTER TABLE MODIFY PARTITION STORAGE ( next pctincrease );

For a temporary segment problem:

ALTER TABLESPACE DEFAULT STORAGE (initial next pctincrease );

E. Resize the Datafile

ALTER DATABASE DATAFILE '' RESIZE ;

F. Defragment the Tablespace

If you would like more information on fragmentation, the following documents are available from Oracle WorldWide Support . Note that this is not a comprehensive list.

Note 1020182.6 Script to Detect Tablespace Fragmentation
Note 1012431.6 Overview of Database Fragmentation
Note 30910.1 Recreating Database Objects

References

Note 1069041.6 - How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts
Note 151994.1 - Overview Of ORA-01653: Unable To Extend Table %s.%s By %s In Tablespace %s
Note 177334.1 - Overview of Temporary Segments
Note 188610.1 - DBA_FREE_SPACE Does not Show Information about Temporary Tablespaces
Note 19045.1 - OERR: ORA 1650 unable to extend rollback segment by in tablespace
Note 19046.1 - OERR: ORA 1651 unable to extend save undo segment by %s.%s in tablespace %s
Note 19047.1 - OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s
Note 19048.1 - OERR: ORA 1653 unable to extend table %s.%s by # in tablespace %s
Note 19049.1 - OERR: ORA 1654 unable to extend index by for tablespace Note 19050.1 - OERR: ORA 1655 unable to extend cluster %s.%s by %s for tablespace %s
Note 48843.1 - OERR: ORA 1683 unable to extend index %s.%s partition %s by %s in tablespace %
Note 48848.1 - OERR: ORA 1688 unable to extend table %s.%s partition %s by %s in tablespace %
Note 48851.1 - OERR: ORA 1691 unable to extend lob segment %s.%s by %s in tablespace %s
Note 48852.1 - OERR: ORA 1692 unable to extend lob segment %s.%s partition %s by %s in tables
Note 75184.1 - OERR: ORA-3233 unable to extend table %s.%s subpartition %s by %s in tablespac
Note 75185.1 - OERR: ORA-3234 unable to extend index %s.%s subpartition %s by %s in tablespac
Note 75188.1 - OERR: ORA-3238 unable to extend LOB segment %s.%s subpartition %s by %s in tab

How to Diagnose and Resolve UNABLE TO EXTEND Errors