Introduction:
Database suddenly reported: ORA-01654: unable to extend index BO. INDEX_indexname by 311072 in tablespace error. There is only one reason to check the Internet, which is to increase the size of tablespace. because the tablespace of the database is large enough, it is not found that the tablespace is insufficient, but the parameter is incorrect. the methods to solve this problem are summarized as follows.
Method 1:
When a similar error occurs, first check whether the space of tablespace is large enough. If it is not large enough, it means that the space of tablespace is not extended. In this case, you need to increase the size of datafile of tablespace, the method is very simple. I will not talk about it anymore, or add a new datafile to this tablespace. Use alter tablespace mytablespace to add datafile 'xxx' size xxxx.
Method 2:
This is my problem. the size of my datafile is 2000 m, the next extent of my index is 2G, and the pct increase is 50, so that the next extent to be extended is 3G, the Size of my datafile is 2 GB, so no 3G space is found. Of course, an error will occur.
After the problem is found, it is easy to solve the problem. Modify next extent to 128 k and pct increase to 0.
I don't know who set it. It's a low-level error.
--------------------------------------------- Split line ---------------------------------------------
Symptom:
The test database uses the following method to create an index:
Create index IDX_ANA_OFFICE on ANA (OFFICE_CITY, OFFICE_NO)
Tablespace IDX
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initialize 128 K
Next 128 K
Minextents 1
Maxextents unlimited
Pctincrease 0
);
Error: ORA-01654: unable to extend index GALT. IDX_OFFICE by 128 in tablespace IDX changed to default create: create index IDX_ANA_PNR_OFFICE on ANA (OFFICE_CITY, OFFICE_NO)
Tablespace IDX;
Check whether the SQL statement is: storage (initial 64 Knext 1 Mminextents 1 maxextents unlimited );
Problem tracing: 1. First, the error 1654 is reported. MOS introduces the error as follows:
Error: ORA-01654 Text: unable to extend index %s.%s by %s in tablespace %s -------------------------------------------------------------------------------Cause: Failed to allocate extent for index segment in tablespace.Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace
(1) we recommend that you use the DBA_FREE_SPACE view to query the insufficient tablespace (Note: 121259.1 provides several scripts ). (2) In addition, the DBA_INDEXES view describes the size of the next partition (NEXT_EXTENT) and the percentage increase of all indexes (PCT_INCREASE ). "Next_extent" refers to the partition size to be allocated (that is, the content involved in the error ). Partition allocation calculation: next_extent = next_extent * (1 + (pct_increase/100) describes How Extents Are AllocatedOracle uses different algorithms to allocate extents in Concept, depending on whether they are locally managed or dictionary managed. with locally managed tablespaces, oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. if that datafile does not have enough adjacent free space, then Oracle looks in another datafile.
MOS also proposes several possible solutions:
Possible solutions:------------------- Manually coalesce adjacent free extents: ALTER TABLESPACE <tablespace name> COALESCE; The extents must be adjacent to each other for this to work.- Add a datafile: ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' SIZE <integer> <k|m>; - Resize the datafile: ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>; - Enable autoextend: ALTER DATABASE DATAFILE '<full path and file name>' AUTOEXTEND ON MAXSIZE UNLIMITED;- Defragment the Tablespace- Lower "next_extent" and/or "pct_increase" size: ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m> pctincrease <integer>);
I think the following sentence is important: "This error does not indicate whether there is enough space in the tablespace. It only means that Oracle cannot find a large enough continuous space to match the next extent.
2. Another article "troubleshooting guide (TSG)-unable to create/EXTEND Errors" describes various Errors related TO "unable to create/EXTEND. The error of "unable to extend" means that when there is not enough continuous space to allocate segments. I. Put forward the information needed to solve this error: (1) determine the maximum continuous space in the tablespace where the error is reported. SELECT max (bytes) FROM dba_free_space WHERE tablespace_name = '<tablespace name> ';
This SQL statement returns the maximum continuous block size allowed by the tablespace. (DBA_FREE_SPACE Does not return Information about the Temporary tablespace. For details, see "DBA_FREE_SPACE Does not Show Information about Temporary Tablespaces (Document ID 188610.1)". This article describes how to view the continuous block size of the Temporary tablespace ). If the preceding SQL statement is executed immediately after the error is reported, the maximum number of consecutive blocks in the returned tablespace is smaller than the next extent space that the object is trying to allocate. (2) determine the NEXT_EXTENT size. A) use the following SQL statement to manage the tablespace (DMT) in the dictionary of PCT_INCREASE = 0 or use the local management tablespace (LMT) managed in the unified UNIFORM area: SELECT NEXT_EXTENT, PCT_INCREASE
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = <segment name>
AND SEGMENT_TYPE = <segment type>
And owner = <owner>
AND TABLESPACE_NAME = <tablespace name>; segment_type is displayed in the error message, which may contain the following types of segments: CLUSTER
INDEX
INDEX PARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TYPE2 UNDO
Type 2 UNDO (ORA-1651) Similarly, segment_name can be found in the error message. B) Local Management tablespace (LMT) managed using SYSTEM | AUTOALLOCATE ). There is no way to query its next extent size. Only error messages can be queried. The number of blocks in the error message is multiplied by the block size of the tablespace to determine the size of the partition to be created. C) Manage the tablespace (DMT) in the dictionary of PCT_INCREASE> 0 ). SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '<tablespace name> ';
Use the following formula to calculate the size of the partition 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 Note: The ORA-01650 Rollback Segmentpct_increase is only used for earlier versions of Oracle, in later versions, the default value of pct_increase for rollback segments is 0. ORA-01652 Temporary Segment
The temporary segments are the same as the storage default values created in the tablespace. If an error occurs in the query, You need to determine whether the query statement is as optimal as possible to complete sorting. (3) Determine whether the tablespace contains AUTOEXTENSIBLE and has reached MAXSIZ. For data files: SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name = '<tablespace name> ';
For temporary files: SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name = '<tablespace name> ';
(4) determine which solution is optimal. �� If the capacity of next extent (step 2 or 3) is greater than the continuous block with the largest idle space, Manually Coalesce Adjacent Free Extents is an option. If coalesce still does not have enough continuous space, other options may be required. If the volume of the tablespace data file/temporary file has enough space, it may be useful to add the data file/temporary file or eliminate the tablespace fragmentation and add the file to the new volume. If the tablespace is AUTOEXTENSIBLE and has MAXSIZE, you need to increase the maximum capacity (ensure there is sufficient volume space), add data files/temporary files, or eliminate fragmentation. If the capacity of next extent (step 2 or 3) is smaller than the continuous block with the largest free space, contact Oracle Support. II. Possible solutions: (1) manually merge adjacent idle zones. Alter tablespace <tablespace name> COALESCE;
(2) modify one or more data files/temporary files to use AUTOEXTEND. Alter database datafile | tempfile' <full path and name> 'autoextend on maxsize <integer> <k | m | g |
Note: we strongly recommend that you specify the MAXSIZE parameter to prevent data files/temporary files from consuming all available space on the volume. (3) add data files/temporary files. Alter tablespace <tablespace name> add datafile | tempfile' <full path and file name> 'size <integer> <k | m | g | t | p | e>; (4) if the segment is a dictionary table space, you can reduce the sizes of "next_extent" and/or "pct_increase. For non-temporary and non-partition segments: ALTER <segment type> <segment_name> STORAGE (next <integer> <k | m | g | t | p | e> pctincrease <integer> );
For non-temporary and partition segments: alter table <table_name> modify partition <partition_name> STORAGE (next <integer> <k | m | g | t | p | e> pctincrease <integer> );
For temporary segments: alter tablespace <tablespace name> default storage (initial <integer> <k | m | g | t | p | e> next <integer> <k | m | g | t | p | e> pctincrease <integer> );
(5) resizes data files and temporary files. Alter database datafile | tempfile' <full path and file name> 'resize <integer> <k | m | g | t | p | e>;
(6) Eliminate tablespace fragments. Appendix: error related to this solution: 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-1658: unable to create INITIAL extent for segment in tablespace % s
Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.
Action: Use alter tablespace add datafile to add additional space to the tablespace or retry with a smaller value for INITIAL
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
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 cocould 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.
Summary: for the errors in the above case, the problem is caused by insufficient space. The second SQL statement can be used, the reason may be that the capacity of the continuous blocks that can be idle space is satisfied under this parameter value setting. The above method is used to reduce the extent allocation size, in addition, the methods mentioned above can be used to expand files, modify parameter values, and eliminate fragmentation.
ORA-01172, ORA-01151 error handling
ORA-00600 [2662] troubleshooting
Troubleshooting for ORA-01078 and LRM-00109
Notes on ORA-00471 Processing Methods
ORA-00314, redolog corruption, or missing Handling Methods
Solution to ORA-00257 archive logs being too large to store
