ORA-01652: unable to extend temp segment by 8192...

The latest error message "unable to extend temp segment by 8192 in tablespace..." was prompted during rebuild index. This is a common error. When creating an index, you need to use the user's default temporary tablespace for sorting and generating temporary segments in the index tablespace. If the current index tablespace limits automatic expansion or has reached the maximum value of the data file, this error message appears. The following describes the specific analysis and solutions.

 

1. error message

Alter index err ORA-01652: unable to extend temp segment by 8192 in tablespaceGX_ARCHIVE_IDXDECLARE * ERROR at line 1: ORA-01652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDXORA-06512: at line 90 # The following information comes from alert logSun Mar 30 03:08:51 2014ORA-1652: unable to extend temp segment by 128 in tablespace GX_ARCHIVE_IDXORA-1652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDX # fault environment SQL> select * from v $ version where rownum <2; BANNER implements Oracle Database 10g Release 10.2.0.3.0-64bit ProductionSQL> ho cat/etc/issueWelcome to SUSE Linux Enterprise Server 10 SP4 (x86_64)-Kernel \ r (\ l ).

2. ORA-1652 errors
Error: ORA-1652
Text: unable to extend temp segment by % s in tablespace % s
------------------------------------------------------------------------------
Cause: Failed to allocate an extent for temp segment in tablespace.
Action: Use alter tablespace add datafile statement to add one or more
Files to the tablespace indicated or create the object in another
Tablespace.

* ** Important: The notes below are for experienced users-See Note: 22080.1

Explanation:
This error is fairly self explanatory-we cannot get enough space for a temporary segment.
The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

NOTE: A "temp segment" is not necessarily a SORT segment in a temporary tablespace.
It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.
Eg: When you perform a create index a TEMP segment is created to hold what will be the final permanent index data.
This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the create index operation.
It remains a temp segment for the duration of the create index operation and so failures to extend
It report ORA-1652 rather than an INDEX related space error.

Use of temporary segments
A temporary segment may be from:
1) a sort Used for a SELECT or for DML/DDL
2) create index The index create performs a SORT in the users default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX tablespace.
Once the index build is complete the segment type is changed.
3) CREATE PK CONSTRAINT
4) ENABLE CONSTRAINT
5) create table New tables start out as TEMPORARY segments.
Eg: If MINEXTENTS is> 1 or you issue CREATE table as SELECT.
6) Accessing a global temporary table When you access a global temporary table a TEMP segment is instantiated to hold the temporary data.

 

3. TROUBLESHOOTING ORA-01652 (Reference Doc ID 1267351.1)

# The following are two scenarios where temporary segments cannot be extended:
EXAMPLE 1:

Temporary tablespace TEMP is being used and is 50 gb in size (a recommended minimum for 11g)

TIME 1: Session 1 starts a long running query
TIME 2: Session 2 starts a query and at this point in time Session 1 has consumed 48 gb of TEMP's free space
TIME 3: Session 1 and Session 2 receive a ORA-1652 because the tablespace has exhausted of its free space
Both sessions fail .. and all temp space used by the sessions are freed (the segments used are marked FREE for reuse)
TIME 4: SMON cleans up the temporary segments used by Session 1 and Session 2 (deallocates the storage)
TIME 5: Queries are run against the views V $ SORTSEG_USAGE or V $ TEMSEG_USAGE and V $ SORT_SEGMENT... and it is found that no space is being used (this is normal)

 

EXAMPLE 2:

Permanent tablespace INDEX_TBS is being used and has 20 gb of space free # the problem that temporary tablespace cannot be expanded at this time is 2nd Cases

TIME 1: Session 1 begins a create index command with the index stored in INDEX_TBS
TIME 2: Session 1 exhausts all of the free space in INDEX_TBS as a result the create index abends
TIME 3: SMON cleans up the temporary segments that were used to attempt to create the index
TIME 4: Queries are run against the views V $ SORTSEG_USAGE or V $ TEMSEG_USAGE... and it is found that the INDEX_TBS has no space used (this is normal)

# Solution Section
First it is important to forget what is known about past behavior of the instance as the current tablespace size is insufficient to handle the demand by current sessions

There are three recommended methods of supplying sufficient storage space to meet the needs of current sessions by increasing the size of your temporary tablespace

 

1) Set one or more of the tempfiles | datafiles for the tablespace to AUTOEXTEND with MAXSIZE set... so that you do not exhaust all available disk volume space
(Discuss this with a System Administrator)

After a month or so of having this setting... AUTOEXTEND can be disabled... as it is likely that the system has found its true high watermark for temporary segment usage

(This is the most recommended method as it allows the database instance to find its own high watermark)

 

2) Monitor the temporary segment usage via queries like

SELECT sum (blocks) * <block size of the temporary tablespace>
FROM v $ tempseg_usage
WHERE tablespace = '<name of the temporary tablespace> ';

And resize one or more of the tempfiles | datafiles for the tablespace as the tablespace becomes close to exhausted

 

3) Add a tempfile | datafile to the temporary tablespace with the problem and monitor usage as described in #2

Another good idea is to monitor temporary tablespace usage over time to determine what queries are consuming the temporary space

For example: How Can Temporary Segment Usage Be Monitored Over Time? (Docid 364417.1)
This note was written to monitor temporary tablespaces .. but may be able to be modified to also monitor permanent tablespaces

 

4. fault solution in this case

SQL> @ temp_sort_segment. SQL + ==================================== ========================================================== ========+ | Segment Name: the segment name is a concatenation of the | SEGMENT_FILE (File number of the first extent) | and | SEGMENT_BLOCK (Block number of the first extent) | Current Users: number of active users of the segment | Total Temp Segment Size: Total size of the temporary seg Ment in bytes | Currently Used Bytes: Bytes allocated to active sorts | Extent Hits: Number of times an unused extent was found in the pool | Max Size: maximum number of bytes ever used | Max Used Size: Maximum number of bytes used by all sorts | Max Sort Size: Maximum number of bytes used by an individual sort | Free Requests: number of requests to deallocate | + ======================== ========================================================== ===========================+ Tablespace Segment Current Total Temp Currently Pct. extent Max Used Max Sort Free Name Users Segment Size Used Bytes Used Hits Size Requests -------------- -------- ------- hour ---- -------- hour ------------------ TEMP SYS.0.0 0 29,570,891,776 0 17,230 29,570,891,776 29,570,891,776 29,569,843,200 0GOEX_TEMP SYS.0.0 12 24,135,073,792 12,582,912 0 214,932 24,135,073,792 4,908,384,256 2,960,130,048 0 *************** ------- ------------------------ -------- -------------------- sum 12 53,705,965,568 12,582,912 232,162 53,705,965,568 34,479,276,032 32,529,973,248 0 -- as shown in the preceding query, the temp temporary tablespace consumption of the current instance has reached 29,570, 8 91,776, equal to Total Temp Segment Size -- currently, we use the sys account to rebulid index, and the sys account uses the default temporary tablespace temp. SQL> @ temp_sort_users. SQL --> This query queries which sessions are currently using temporary segments, the result is the same as the above 12,582,912 Tablespace Name Username SID Serial # Contents Segment Type Extents Blocks Bytes partition failed ------ --------- ------------ -------- ------------ GOEX_TEMP limit 1079 39023 TEMPORARY LOB_DATA 1 128 limit 1,048,576 22320 TEMPORARY LOB_DATA 1 128 1,048,576 GOEX_WEBUSER 1075 15301 TEMPORARY LOB_DATA 1 128 1,048,576 million 1056 22505 TEMPORARY LOB_DATA 1 128 1,048,576 GOEX_WEBUSER 1046 17617 TEMPORARY LOB_DATA 1 128 1,048,576 million 1042 TEMPORARY LOB_DATA 1 30925 128 GOEX_WEBUSER 1,048,576 1041 TEMPORARY LOB_DATA 1 10180 million 128 million 1,048,576 20315 TEMPORARY LOB_DATA 1 128 1,048,576 GOEX_WEBUSER 1034 19147 TEMPORARY LOB_DATA 1 128 1,048,576 GOEX _ WEBUSER 1028 6362 TEMPORARY LOB_DATA 1 128 1,048,576 GOEX_WEBUSER 1027 12614 TEMPORARY LOB_DATA 1 128 1,048,576 GOEX_WEBUSER 1022 23077 TEMPORARY LOB_DATA 1 128 1,048,576 *************** -------- ------------ sum 12 1,536 12,582,912 -- let's take a look at the index on the GX_ARCHIVE_IDX tablespace. SQL> SELECT * 2 FROM (SELECT segment_name, bytes/1024/1024/1024 AS size_g, extents 3 FROM dba_segments 4 WHERE tablespace _ Name = 'gx _ ARCHIVE_IDX '5 order by 2 DESC) t 6 where rownum <3; SEGMENT_NAME SIZE_G Extents indexes ---------- -------- PK_ACC_POS_STOCK_ARCH_TBL 25.9765625 limit 3.97167969 177 -- the preceding query has a large index close to 26 GB. The problem should be caused by this large index. This large index is another topic and will not be described again. -- It should be sufficient based on the current temporary tablespace situation. -- View the usage of the temporary segment described above. 2. The create index part also has temp segment on the INDEX tablespace. Therefore, the alert Log report cannot be extend temp segmentSQL> @ tbs_free_single.sqlEnter value for input_tablespace_name: GX_ARCHIVE_IDXold 22: and t. TABLESPACE_NAME = upper ('& input_tablespace_name') new 22: and t. TABLESPACE_NAME = upper ('gx _ ARCHIVE_IDX ') TABLESPACE_NAME USED_MB FREE_MB TOTAL_MB PER_FR %-------- -------- ------ %45,912 19,037 64,949 29% SQL >@%value for input_tbsname: %26: AND t. tablespace_name = UPPER ('& input_tbsname') new 26: AND t. tablespace_name = UPPER ('gx _ ARCHIVE_IDX ') TABLESPACE_NAME FILE_ID USED_MB FREE_MB TOTAL_MB PER_FR bytes ---------- -------- ------ limit 25 29,328 2,916 32,244 9% 16,584 16,121 SQL> select file_id, file_name, autoextensible from dba_data_files where file_id in (25, 40 ); FILE_ID FILE_NAME AUT ---------- plugin --- 25/u02/database/CABO3/oradata/CABO3_archive_idx.dbf NO 40/u02/database/CABO3/oradata/plugin YES -- according to the solution of 1267351.1, add a new data file SQL> alter tablespace GX_ARCHIVE_IDX add datafile '/u02/database/CABO3/oradata/CABO3_archive_idx3.dbf '2 size 2g autoextend on; Tablespace altered. -- this exception does not occur after a data file is added to the tablespace.

References
RMAN configuration retention policy

Oracle Flash recovery area)

Snapshot control file)

Backup and recovery solution for small and medium databases rman catalog (1)

Backup and recovery solution for small and medium databases rman catalog (2)

Backup and recovery solution for small and medium databases rman catalog (III)

Block RECOVERY BASED ON RMAN)

Use DBMS_REPAIR to fix Bad blocks

Method for converting the cloned file location in the RMAN Database

RMAN duplicate)

RMAN-based database cloning on the same machine

User-managed database clone

Cloning from an active database based on RMAN (rman duplicate from active DB)

RMAN duplicate from active encountered ORA-17627 ORA-12154

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management

SYSTEM tablespace management and Backup Recovery

SYSAUX tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion

Customize the date and time format displayed by RMAN

Backup and recovery of read-only tablespace

Incomplete recovery of Oracle based on user management

Understanding using backup controlfile

Use RMAN for recovery from different machine backups (WIN platform)

Use RMAN to migrate a file system database to ASM

Oracle backup policy (RMAN) in Linux)

Linux RMAN backup shell script

Use RMAN to migrate the database to a different machine

Run the SQL statement at the RMAN prompt.

Oracle RMAN-based incomplete recovery (incomplete recovery by RMAN)

Rman restores archivelog)