Solution to oracle Database ORA-01654 errors, oracleora-01654

Solution to oracle Database ORA-01654 errors, oracleora-01654

Introduction:

Database suddenly reported: ORA-01654: unable to extend index BO. INDEX_indexname by 311072 in tablespace error, online query causes, found that there is only one solution, on

Is to increase the size of tablespace. 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

The size increases. The method is 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
 
To be created by default:
Create index IDX_ANA_PNR_OFFICE on ANA (OFFICE_CITY, OFFICE_NO)
Tablespace IDX;
 
Check the SQL statement as follows:
Storage
(
Initial 64 K
Next 1 M
Minextents 1
Maxextents unlimited
);

Problem tracking:
1. First, the 1654 error 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

The size of the partition to be allocated (that is, the content involved in the error ).
Partition allocation calculation: next_extent = next_extent * (1 + (pct_increase/100 ))
The Concept describes the algorithm used to allocate segments.
How Extents Are Allocated
Oracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.

Locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in

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 sufficient 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 that reports an error.
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 a 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>;
Here, segment_type is displayed in the error message and may contain the following types of segment:
CLUSTER
INDEX
INDEX PARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TYPE2 UNDO
TYPE2 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 partition size to be allocated:
Extent size = next_extent * (1 + (pct_increase/100)
For example:
Next_extent = 512000
Pct_increase = 50
Next extent size = 512000*(1 + (50/100) = 512000*1.5 = 768000
Note:
ORA-01650 Rollback Segment
Pct_increase is only used in earlier versions of Oracle. The default value of pct_increase for rollback segments in later versions 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 Free space, Manually Coalesce Adjacent Free Extents is an option. If coalesce is still inadequate

Other options may be required if there is enough continuous space.
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-managed tablespace, the "next_extent" and/or "pct_increase" sizes can be reduced.
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: errors related to such solutions:
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:
In general, the error in the above case is caused by insufficient space. The reason why the second SQL statement can be used may be the capacity of continuous blocks that can be idle space under the parameter value settings, the

Is to reduce the extent allocation size, in addition to the above mentioned methods such as expanding files, modifying parameter values, and eliminating fragmentation can be used.



Original Site: http://www.linuxidc.com/Linux/2013-12/93685.htm