How to Diagnose and Resolve UNABLE TO EXTEND Errors
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@]
Information in this document applies to any platform.
" 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.
1. Determine the largest contiguous space available for the tablespace with the error
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
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
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.
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
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
There are several options for resolving UNABLE TO EXTEND errors
The extents must be adjacent to each other for this to work.
For non-temporary and partitioned segments:
For a temporary segment problem:
Note 1020182.6 Script to Detect Tablespace Fragmentation
Note 1012431.6 Overview of Database Fragmentation
Note 30910.1 Recreating Database Objects
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 | ||||
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.7Information 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 tablespaceEXAMPLE 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, 2008Instructions 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 =
Possible Segment Types:
CLUSTER
INDEX
INDEX PARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TYPE2 UNDO
TYPE2 UNDO (ORA-1651)
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 FilesSELECT 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 SortsNote 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
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
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1138527/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORA-1652: unable to extend temp segment errors In RACError
- How to recover from USB pipe errorsError
- sudo 出現unable to resolve host 解決方法
- eslint-plugin-import Unable to resolve path to moduleEsLintPluginImport
- Android Unable to resolve target 'android-X'Android
- How to resolve the problem of no Oracle instances were discovered in BEOracle
- ORA-1652: unable to extend temp segment by 256 in tablespace PSAPTEMPAPT
- What is a deadlock and how does one fix deadlock errors?Error
- How to resolve : Authentication denied: Boot identity not validbootIDE
- oracle表空間不足:ORA-01653: unable to extend tableOracle
- sudo時出現unable to resolve host 的解決方法
- How to Resolve Invalid Objects in a Database [ID 158185.1]ObjectDatabase
- 【故障處理】ORA-1688: unable to extend table AUDSYS.AUD$UNIFIEDNifi
- Oracle - ORA-01652: unable to extend temp segment by 128 in tablespace TEMPOracle
- AndroidStudio升級報錯Unable to resolve dependency for ':app@debug/compileClasspath': Could not resolve cAndroidAPPCompile
- c# Api 錯誤 Unable to resolve service for type while attempting to activate C#APIWhile
- How to Create a BNE Log For Web Adi Issues and Errors? [ID 817023.1]WebError
- Oracle - ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'Oracle
- ORA-1652: unable to extend temp segment by 128 in tablespace錯誤的解決方法
- How to resolve ORA-19706 error when select from dblinkError
- vue3 引入 element-ui 報錯 - ERR! ERESOLVE unable to resolve dependency treeVueUI
- $.extend()和$.fn.extend()區別
- $.extend()和$.fn.extend()函式用法函式
- jquery.fn.extend與jquery.extendjQuery
- SCSS @extendCSS
- 理解jquery的$.extend()、$.fn和$.fn.extend()jQuery
- ORA-1653: unable to extend table by 1024 in tablespace(oracle表空間滿了的解決方案)Oracle
- jQuery.extend()jQuery
- jQuery.extend和jQuery.fn.extend的區別jQuery
- jquery的$.extend和$.fn.extend作用及區別jQuery
- jQuery - 函式 $.extend 和 $.fn.extend 的說明jQuery函式
- SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement [ID 215187.1]SQLAI
- jQuery外掛開發中$.extend和$.fn.extend辨析jQuery
- 如何解決flash跑到層上面的問題?How to resolve the div layer appear over the top of flash?...APP
- 理解jquery的$.extend()jQuery
- jQuery.fn.extend()jQuery
- Metric Collection ErrorsError
- oracle之errorsOracleError