Troubleshooting of table space problems in ORA-3233

Symptom: The test database uses the following method to create an index: createindexIDX_ANA_OFFICEonANA (OFFICE_CITY, OFFICE_NO) tablespaceIDXpct

Symptom: create index IDX_ANA_OFFICE on ANA (OFFICE_CITY, OFFICE_NO) tablespace IDX pct

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 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 ))

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. 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 COALESCE;
The extents must be adjacent to each other for this to work.

-Add a datafile:
ALTER TABLESPACE Add datafile' Name> 'size ;

-Resize the datafile:
Alter database datafile' 'Resize ;

-Enable autoextend:
Alter database datafile' 'Autoextend ON
Maxsize unlimited;

-Defragment the Tablespace

-Lower "next_extent" and/or "pct_increase" size:
ALTER STORAGE (next
Pctincrease );
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 =' ';

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 =
AND SEGMENT_TYPE =
And owner =
AND 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 =' ';
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.

Related reading:

Oracle ORA-01555 snapshot old description

Troubleshooting for ORA-01078 and LRM-00109

ORA-01555 ultra-long Query Duration time

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

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

For temporary files:

SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name =' ';

(4) determine which solution is optimal.