ORACLE ORA-1652的解決方法

不一樣的天空w發表於2019-12-06
Oracle

前言：在檢查資料庫的alert日誌，發現資料庫報了ORA-1652: unable to extend temp segment的錯誤，以下記錄的是整個處理過程：

 

1、檢查當前資料庫的表空間的大小，指令碼如下

select file_name,file_id,bytes/1024/1024,status,autoextensible TABLESPACE_NAME from DBA_TEMP_FILES;

 image

 

2、當前資料庫的temp表空間已經設定32GB了，一般情況下如果臨時表空間在20GB左右就夠了（需要根據資料庫的表數量級作為判斷標準）

根據對系統的瞭解，初步判斷這個增長明顯是異常行為；

（如果表空間太小的話，可以通過語句增加：ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 10240M AUTOEXTEND OFF;）

 

3、因為每次報錯都是在凌晨，所以不可能不睡覺一直跟蹤臨時表空間的使用情況，還好可以使用Oracle診斷事件跟蹤ORA-1652事件,該診斷事件對系統的效能影響很小，因為只有在發生這個錯誤的時候，系統才會寫入資訊到alert日誌中；

 

跟蹤的級別分為三個等級：

  • 在session級別啟用資料庫的跟蹤：ALTER SESSION SET EVENTS '1652 trace name errorstack';
  • 在系統級別啟用資料庫的跟蹤：ALTER SYSTEM SET EVENTS '1652 trace name errorstack'; 
  • 把該引數寫入到spfile檔案中： ALTER SYSTEM SET EVENT = '1652 trace name errorstack'  SCOPE = SPFILE;
    （關於引數的詳細設定，也整理了一個文件，可以在部落格中搜尋）

 

對應的關閉指令碼如下：

  • ALTER SESSION SET EVENTS '1652 trace name context off';
  • ALTER SYSTEM SET EVENTS '1652 trace name context off';
  • ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*';

 

4、第二天的時候，果然又報錯了，詳細的報錯資訊如下：

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Errors in file '/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/orcl_m000_15204728.trc:

開啟相應的報錯資訊如下：

*** 2015-02-04 00:12:07.836
*** SESSION ID:(556.17195) 2015-02-04 00:12:07.836
*** CLIENT ID:() 2015-02-04 00:12:07.836
*** SERVICE NAME:(SYS$BACKGROUND) 2015-02-04 00:12:07.836
*** MODULE NAME:(MMON_SLAVE) 2015-02-04 00:12:07.836
*** ACTION NAME:(0000010 FINISHED190) 2015-02-04 00:12:07.836
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP
----- Current SQL Statement for this session (sql_id=2d1p0p5k3f8fu) -----
select p, NULL, NULL from (select count(*) p from v$rman_status  where operation = 'BLOCK MEDIA RECOVERY')
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object

經過以上跟蹤，終於知道了導致臨時表空間耗光的罪魁禍首的原因，接下來是對語句進行分析；

 

5、另外需要注意的是，以上的跟蹤出來的情況並不一定是主要原因，可能是壓垮駱駝的最後一根稻草，因為前面有一條語句用了95%的TEMP表空間，緊接著又有一條語句用了6%的TEMP表空間，這個時候系統會記錄第二條語句。但是真正的原因是前面那條使用了95%TEMP的表空間的語句；

在正常的情況下，可以監控表空間裡面的內容的佔用情況，也能分析出問題的原因，指令碼如下：

SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       T.blocks * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       T.sqladdr address,
       Q.hash_value,
       Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
   AND T.sqladdr = Q.address(+)
   AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
image

可以發現SID/SERIAL為5/1273的SESSION消耗了大量的temp表空間；

 

6、以上提供了整個問題的解決方法，建議大家動手試下，實驗的步驟如下：

a、建立一個大表；

b、進行索引的建立；

c、診斷事件跟蹤ORA-1652事件的開啟；

d、再次建立索引；

e、檢查報警日誌；

 

總結：學習就是不斷實驗和總結的一個過程，每次動手解決記錄問題的過程總是樂趣無窮；

......................................................................................................................................................................………………………………………

本文作者：JOHN，某上市公司DBA，業餘時間專注於資料庫的技術管理，從管理的角度去運用技術。

ORACLE技術部落格：ORACLE 獵人筆記               資料庫技術群：367875324 （請備註ORACLE管理 ） 

......................................................................................................................................................................………………………………………

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12679300/viewspace-1426763/，如需轉載，請註明出處，否則將追究法律責任。

相關文章