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

      最近在rebuild index時提示unable to extend temp segment by 8192 in tablespace..的錯誤提示。這個是個比較常見的錯誤。索引在建立的時候需要使用到該使用者預設的暫存資料表空間進行排序，以及在索引資料表空間產生臨時段。如果當前的索引資料表空間限制了自動擴充或者已經達到了資料檔案的最大值，此錯誤提示便會出現。下面是具體的分析及其解決過程。

 

1、錯誤提示資訊

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#下面的資訊來自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#故障環境SQL> select * from v$version where rownum<2;BANNER----------------------------------------------------------------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錯誤
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.

臨時段被使用的情形
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)

#下面是無法擴充臨時段的2種情形
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 #此時無法擴充暫存資料表空間的問題當屬第2種情形

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部分
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、本案例故障解決方案  

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        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--從上面的查詢中可知，當前執行個體的temp暫存資料表空間曾耗用量達到29,570,891,776，等於Total Temp Segment Size--當前我們使用sys帳戶來rebulid index，sys帳戶使用的是預設的暫存資料表空間temp。SQL> @temp_sort_users.sql  -->這個查詢是查詢當前哪些session正在使用臨時段，其結果與上面的一致為12,582,912Tablespace 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--那我們來看看GX_ARCHIVE_IDX資料表空間上索引的情形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      540PK_ACC_POS_CASH_PL_ARCH_TBL                                       3.97167969      177--上面的這個查詢盡然有一個接近26GB的大索引，問題應該是由於這個大索引引起的。至於這個這麼大的索引是另外一個話題，不再次描述。--根據當前的暫存資料表空間的情形來看應該是夠的。--查看前面描述的 臨時段被使用的情形2 CREATE INDEX部分在INDEX  tablespace上也會有temp segment--所以alert日誌報告無法在GX_ARCHIVE_IDX 上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------------------------------ -------- -------- -------- ------GX_ARCHIVE_IDX                 45,912   19,037   64,949   29 %SQL> @tbs_free_by_file_id.sqlEnter value for input_tbsname: GX_ARCHIVE_IDXold  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--根據1267351.1的solution，我們為GX_ARCHIVE_IDX資料表空間添加一個新的資料檔案SQL> alter tablespace GX_ARCHIVE_IDX add datafile '/u02/database/CABO3/oradata/CABO3_archive_idx3.dbf'  2  size 2g autoextend on;Tablespace altered.--為該資料表空間增加資料檔案後，無此異常

    

相關參考
    RMAN 配置保留原則    

    Oracle 閃回區(Oracle Flash recovery area)

    Oracle 快照控制檔案(snapshot control file)

    中小型資料庫 RMAN CATALOG 備份恢複方案(一)

    中小型資料庫 RMAN CATALOG 備份恢複方案(二)

    中小型資料庫 RMAN CATALOG 備份恢複方案(三)

    基於RMAN實現壞塊介質恢複(blockrecover)

    用 DBMS_REPAIR 修複壞塊

    RMAN 資料庫複製檔案位置轉換方法

    基於RMAN的異機資料庫複製(rman duplicate)

    基於 RMAN 的同機資料庫複製

    基於使用者管理的同機資料庫複製

    基於RMAN從活動資料庫異機複製(rman duplicate from active DB)

    RMAN duplicate from active 時遭遇 ORA-17627 ORA-12154

    Oracle 冷備份

    Oracle 熱備份

    Oracle 備份恢複概念

    Oracle 執行個體恢複

    Oracle 基於使用者管理恢複的處理

    SYSTEM 資料表空間管理及備份恢複

    SYSAUX資料表空間管理及恢複

    Oracle 基於備份控制檔案的恢複(unsing backup controlfile)

    RMAN 概述及其體繫結構

    RMAN 配置、監控與管理

    RMAN 備份詳解

    RMAN 還原與恢複

    RMAN catalog 的建立和使用

    基於catalog 建立RMAN儲存指令碼

    基於catalog 的RMAN 備份與恢複

    RMAN 備份路徑困惑

    自訂 RMAN 顯示的日期時間格式

    唯讀資料表空間的備份與恢複

    Oracle 基於使用者管理的不完全恢複

    理解 using backup controlfile

    使用RMAN實現異機備份恢複(WIN平台)

    使用RMAN遷移檔案系統資料庫到ASM

    基於Linux下 Oracle 備份策略(RMAN)

    Linux 下RMAN備份shell指令碼

    使用RMAN遷移資料庫到異機

    RMAN 提示符下執行SQL語句

    Oracle 基於 RMAN 的不完全恢複(incomplete recovery by RMAN)

    rman 還原歸檔日誌(restore archivelog)