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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Recently in rebuild index to extend temp segment by unable 8192 in tablespace.. Error. This is a common mistake. The index need to use the default temporary tablespace sort at the time of creation, as well as in the index table space to generate temporary segments. If the current index table space limit the automatic extension or have reached the maximum value of the data file, this error message will appear. The following is the specific analysis and solving process.

1, Error message

alter index err ORA-01652: unable to extend temp segment by 8192 in tablespace
GX_ARCHIVE_IDX
DECLARE
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDX
ORA-06512: at line 90

#The following information from alert log
Sun Mar 30 03:08:51 2014
ORA-1652: unable to extend temp segment by 128 in tablespace                 GX_ARCHIVE_IDX
ORA-1652: unable to extend temp segment by 8192 in tablespace                 GX_ARCHIVE_IDX

#Fault environment
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

SQL> ho cat /etc/issue

Welcome to SUSE Linux Enterprise Server 10 SP4  (x86_64) - Kernel \r (\l).


2, A ORA-1652 error
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.

Temporary segments are used
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)

#Here are 2 kinds of situations cannot extend temporary segments
EXAMPLE 1:

Temporary tablespace TEMP is being used and is 50gb 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 48gb of TEMP's free space
TIME 3 : Session 1 and Session 2 receive an ORA-1652 because the tablespace has exhausted of 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 20gb of space free #The problem is the second kind of situations temporary tablespace cannot be extended at this time

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)

#The following is part of Solution
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 space

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

4, In this case the fault solution

SQL> @temp_sort_segment.sql

+==================================================================================+
| Segment Name            : The segment name is a concatenation of the             |
|                           SEGMENT_FILE (File number of the first extent)         |
|                           and the                                                |
|                           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 segment 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         Max Used         Max Sort     Free
          Name     Name   Users     Segment Size       Used Bytes Used     Hits             Size             Size             Size Requests
-------------- -------- ------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------------- --------
TEMP           SYS.0.0        0   29,570,891,776                0    0   17,230   29,570,891,776   29,570,891,776   29,569,843,200        0
GOEX_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 can be seen from the above query, the current instance of temp temporary tablespace has consumption reached 29570891776, equal to Total Temp Segment Size
--We are currently using the sys account to rebulid index, Sys account using the temporary tablespace temp default. 

SQL> @temp_sort_users.sql  -->This query is a query in which session is using temporary segments, the results with the above agreement of 12,582,912

Tablespace Name Username           SID   Serial# Contents  Segment Type  Extents   Blocks        Bytes
--------------- --------------- ------ --------- --------- ------------ -------- -------- ------------
GOEX_TEMP       GOEX_WEBUSER      1079     39023 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1078     22320 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1075     15301 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1056     22505 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1046     17617 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1042     30925 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1041     10180 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1038     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

--That we have a look the GX_ARCHIVE_IDX tablespace index
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
----------------------------------------------------------------- ---------- --------
PK_ACC_POS_STOCK_ARCH_TBL                                         25.9765625      540
PK_ACC_POS_CASH_PL_ARCH_TBL                                       3.97167969      177
--The query above all have a close to the 26GB index, because the index should be caused by. As for the so big index is another topic, not again to describe. 
--According to the temporary table space current situation should be enough. 
--The temporary section view described above is used in the case of 2 CREATE INDEX part of the INDEX tablespace will also have temp segment
--So the alert log report cannot extend on GX_ARCHIVE_IDX temp segment

SQL> @tbs_free_single.sql
Enter value for input_tablespace_name: GX_ARCHIVE_IDX
old  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
------------------------------ -------- -------- -------- ------
GX_ARCHIVE_IDX                 45,912   19,037   64,949   29 %

SQL> @tbs_free_by_file_id.sql
Enter value for input_tbsname: GX_ARCHIVE_IDX
old  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
------------------------------ ---------- -------- -------- -------- ------
GX_ARCHIVE_IDX                       25   29,328    2,916   32,244    9 %
GX_ARCHIVE_IDX                       40   16,584   16,121   32,705   49 %

SQL> select file_id,file_name,autoextensible from dba_data_files where file_id in(25,40);

   FILE_ID FILE_NAME                                                    AUT
---------- ------------------------------------------------------------ ---
        25 /u02/database/CABO3/oradata/CABO3_archive_idx.dbf            NO
        40 /u02/database/CABO3/oradata/CABO3_archive_idx2.dbf           YES

--According to the 1267351.1 solution, we add a new data file for the GX_ARCHIVE_IDX table space
SQL> alter tablespace GX_ARCHIVE_IDX add datafile '/u02/database/CABO3/oradata/CABO3_archive_idx3.dbf'
  2  size 2g autoextend on;

Tablespace altered.

--Increased data file for the table space, without the exception


 Oracle& nbsp; Niu Pengshe

Related reference
RMAN configure retention policy

Oracle flash recovery area(Oracle Flash recovery area)

Oracle snapshot control file(snapshot control file)

Small and medium-sized database RMAN CATALOG backup and recovery scheme (a)

Small and medium-sized database RMAN CATALOG backup and recovery scheme (two)

Small and medium-sized database RMAN CATALOG backup and recovery scheme (three)

Bad block media recovery based on RMAN(blockrecover)

With the DBMS_REPAIR repair bad block

Cloning of RMAN database file location conversion method

Cloning of different machine database based on RMAN(rman duplicate)

The same machine database clone based on RMAN

The same machine database cloning based on user management

RMAN activity from different database machine based on Clone(rman duplicate from active DB)

RMAN duplicate from active by ORA-17627 ORA-12154

Oracle cold backup

Oracle hot backup

Oracle backup and recovery concepts

The Oracle instance recovery

Oracle user management based on the restoration of

The SYSTEM table space management and backup and recovery

The SYSAUX table space management and restoration

Restore the backup control file based on Oracle(unsing backup controlfile)

Overview of RMAN and its system structure

Monitoring and management, RMAN

RMAN backup

RMAN reduction and recovery

Create a RMAN catalog and use

Catalog creates the RMAN stored scripts based on

Catalog RMAN backup and recovery based on

RMAN backup path confused

Date and time format definition RMAN display

A read-only tablespace backup and recovery

Oracle user management based on incomplete recovery

Understanding using backup controlfile

RMAN realize the use of different machine backup and recovery (WIN platform)

Transfer the file system to the ASM database using RMAN

Oracle backup strategy based on Linux(RMAN)

Linux RMAN shell backup script

The use of RMAN database migration to different machine

The SQL statement to execute the RMAN command prompt

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

The reduction of RMAN archive log(restore archivelog)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Opie at October 12, 2014 - 6:45 PM