當收到告警信息ORA-01652: unable to extend temp segment by 128 in tablespace xxxx 時，如何Troubleshooting ORA-1652這樣的問題呢？ 當然一般xxx是臨時表空間，也有可能是用戶表空間。 我們先來模擬一下這個情況，在... ...
當收到告警信息ORA-01652: unable to extend temp segment by 128 in tablespace xxxx 時，如何Troubleshooting ORA-1652這樣的問題呢？ 當然一般xxx是臨時表空間，也有可能是用戶表空間。
我們先來模擬一下這個情況，在兩個會話視窗執行下麵SQL語句，這個視圖比較特殊（因為比較懶，不想去構造一個大量消耗臨時段的SQL，便使用手頭的一個案例腳本），它裡面有一個DISTINCT操作會消耗TEMP表空間中大量的臨時段
SQL> select count(*) from v_ies_go_information;
開啟兩個會話視窗執行上面這個SQL，此時這兩個會話會耗大量臨時段，那麼你用下麵SQL語句就能捕獲到這個SQL，如下所示：
For 8.1.7 to 9.2
SELECT A.USERNAME, A.SID, A.SERIAL#, A.OSUSER, B.TABLESPACE, B.BLOCKS, C.SQL_TEXT
FROM V$SESSION A, V$SORT_USAGE B, V$SQLAREA C
WHERE A.SADDR = B.SESSION_ADDR
AND C.ADDRESS= A.SQL_ADDRESS
AND C.HASH_VALUE = A.SQL_HASH_VALUE
ORDER BY B.TABLESPACE, B.BLOCKS;
For 10.1 and above:
COL USERNAME FOR A16;
COL OSUSER FOR A16;
COL TABLESPACE FOR A10;
COL SQL_TEXT FOR A160;
SELECT A.USERNAME, A.SID, A.SERIAL#, A.OSUSER, B.TABLESPACE, B.BLOCKS, C.SQL_TEXT
FROM GV$SESSION A, GV$TEMPSEG_USAGE B, GV$SQLAREA C
WHERE A.SADDR = B.SESSION_ADDR
AND C.ADDRESS= A.SQL_ADDRESS
AND C.HASH_VALUE = A.SQL_HASH_VALUE
ORDER BY B.TABLESPACE, B.BLOCKS;
當然消耗臨時表空間的BLOCKS是一直變化的，下麵只是其中一次查詢結果的截圖
當然這個也可以通過下麵SQL查詢當前消耗TEMP臨時段的SQL_ID以及具體大小信息。這些信息都是實時變化的。
SQL> SELECT SQL_ID,SUM(BLOCKS) FROM GV$TEMPSEG_USAGE GROUP BY SQL_ID ORDER BY 2 DESC;
SQL_ID SUM(BLOCKS)------------- -----------cw4d8h5fudg6b 456704SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------TEMPSCM2 1048320 506368 541952SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMPSCM2 1048320 1030144 18176
在另外一個視窗，不時執行下麵SQL語句觀察臨時表空間的消耗使用情況，也能看到臨時表空間的消耗變化情況， 如下所示：
SELECT D.TABLESPACE_NAME, SPACE "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS",
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)", SPACE - USED_SPACE "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / ( 1024 * 1024 ), 2) SPACE,
SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE, ROUND(SUM(BLOCKS * 8192) / ( 1024 * 1024 ), 2) USED_SPACE FROM V$SORT_USAGE GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+) AND D.TABLESPACE_NAME='TEMPSCM2'
但是很多時候，當我們收到告警日誌的告警郵件時，其實該SQL語句其實已經結束了。就像我這個測試會話中，如果已經收到ORA-1652 錯誤提示，其實會話已經結束，返回錯誤提示了。
Mon Aug 07 22:23:40 CST 2017
ORA-1652: unable to extend temp segment by 128 in tablespace TEMPSCM2
Mon Aug 07 22:23:40 CST 2017
ORA-1652: unable to extend temp segment by 128 in tablespace TEMPSCM2
此時你用上面SQL其實已經不能捕獲到相關信息了，因為PMON已經釋放、回收了這些會話占用的臨時段，如下所示，測試環境已經查不到任何信息，如果是生產環境，那麼有可能查到是不准確的信息（查到的是非引起問題的SQL）。上面只適合查詢當前臨時表空間的使用情況，而不適合用來追查已經出現的ORA-1652錯誤。
那麼此時我們應該怎麼辦呢？ 其實我們可以使用ASH報告來幫忙定位消耗了大量臨時段的SQL語句，如果收到ORA-01652告警後，最好及時生成一個快照，然後根據告警日誌裡面ORA-01652出現的時間，生成ASH報表，例如，此次試驗ORA-01652出錯的時間為22:23:40，那麼我們生成22：20 ~ 22:25這個時間段的ASH報告。當然這個時間適當調整，儘量縮小範圍，可以精准定位問題SQL。
