最近在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 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 #下面的信息來自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 #故障環境 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).
*** Important: The notes below are for experienced users - See Note:22080.1
Explanation:
NOTE: A "temp segment" is not necessarily a SORT segment in a temporary tablespace.
臨時段被使用的情形
3、TROUBLESHOOTING ORA-01652(Reference Doc ID 1267351.1)
#下面是無法擴展臨時段的2種情形
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
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
#下面是Solution部分
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
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>
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)
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 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
--從上面的查詢中可知，當前實例的temp臨時表空間曾耗用量達到29,570,891,776，等於Total Temp Segment Size
--當前我們使用sys帳戶來rebulid index，sys帳戶使用的是默認的臨時表空間temp。
SQL> @temp_sort_users.sql -->這個查詢是查詢當前哪些session正在使用臨時段，其結果與上面的一致為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
--那我們來看看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 540
PK_ACC_POS_CASH_PL_ARCH_TBL 3.97167969 177
--上面的這個查詢盡然有一個接近26GB的大索引，問題應該是由於這個大索引引起的。至於這個這麼大的索引是另外一個話題，不再次描述。
--根據當前的臨時表空間的情形來看應該是夠的。
--查看前面描述的 臨時段被使用的情形2 CREATE INDEX部分在INDEX tablespace上也會有temp segment
--所以alert日志報告無法在GX_ARCHIVE_IDX 上extend 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
--根據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.
--為該表空間增加數據文件後，無此異常
相關參考
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)
