空間RESUMABLE操作（二）

Oracle從9i開始引入這個功能，當出現空間不足等相關的錯誤時，Oracle可以不是馬上返回錯誤資訊，並回滾當前的操作，而是將操作掛起，直到掛起時間超過RESUMABLE TIMEOUT，或者空間不足的錯誤被解決。

這一篇簡單介紹空間RESUMABLE的相關檢視和包。

空間RESUMABLE操作（一）：http://yangtingkun.itpub.net/post/468/485892

 

 

前面簡單介紹了空間RESUMABLE操作的使用，說明了一個會話如何才能ENABLE RESUMABLE語句，並設定TIMEOUT時間。

那麼隨後問題就是，當資料庫中發生了空間RESUMABLE操作，一些語句處於SUSPEND狀態，由於沒有錯誤資訊的返回，如何判斷這些語句是由於空間不足被掛起，還是處於其他的原因而處於等待狀態。

Oracle提供了USER_RESUMABLE和DBA_RESUMABLE檢視，透過常用的V$SESSION_WAIT檢視同樣可以查詢到SUSPEND狀態。

SQL> SELECT SUM(BYTES)/1024/1024          
  2  FROM DBA_FREE_SPACE
  3  WHERE TABLESPACE_NAME = 'TEST';

SUM(BYTES)/1024/1024
--------------------
            199.9375

SQL> CREATE TABLE T (ID NUMBER)
  2  TABLESPACE TEST
  3  STORAGE (INITIAL 300M);
CREATE TABLE T (ID NUMBER)
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 24 in tablespace TEST

下面設定會話的RESUMABLE並設定TIMEOUT為300秒：

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 300;

Session altered.

在另外的會話中查詢：

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT SESSION_ID, STATUS, TIMEOUT, SUSPEND_TIME, RESUME_TIME, ERROR_NUMBER
  2  FROM DBA_RESUMABLE;

SESSION_ID STATUS       TIMEOUT SUSPEND_TIME         RESUME_TIME          ERROR_NUMBER
---------- --------- ---------- -------------------- -------------------- ------------
       120 NORMAL           300                                                      0

由於會話設定了ENABLE RESUMABLE，因此在DBA_RESUMABLE檢視中，已經可以看到這個會話資訊了，不過目前會話處於正常狀態。

下面執行剛才報錯的SQL語句：

SQL> CREATE TABLE T (ID NUMBER)
  2  TABLESPACE TEST
  3  STORAGE (INITIAL 300M);

會話進入到掛起狀態，在另外的會話檢查DBA_RESUMABLE檢視：

SQL> SELECT SESSION_ID, STATUS, TIMEOUT, SUSPEND_TIME, RESUME_TIME, ERROR_NUMBER
  2  FROM DBA_RESUMABLE;

SESSION_ID STATUS       TIMEOUT SUSPEND_TIME         RESUME_TIME          ERROR_NUMBER
---------- --------- ---------- -------------------- -------------------- ------------
       120 SUSPENDED        300 06/16/09 17:12:53                                 1659

SQL> SELECT SID, EVENT
  2  FROM V$SESSION_WAIT
  3  WHERE SID = 120;

       SID EVENT                                             
---------- --------------------------------------------------
       120 statement suspended, wait error to be cleared     

當語句由於空間問題被掛起後，從DBA_RESUMABLE檢視中可以看到會話的狀態變為RESUPENDED，而且還有會話發生掛起的時間。

從V$SESSION_WAIT檢視或者10g以後的V$SESSION檢視的EVENT列，也可以看到會話處於SUSPEND狀態。

除了解決空間問題使得這個會話恢復正常外，還可以透過DBMS_RESUMABLE包對這個會話進行維護操作，比如使用ABORT中止會話的掛起：

SQL> EXEC DBMS_RESUMABLE.ABORT(120)

PL/SQL procedure successfully completed.

SQL> SELECT SESSION_ID, STATUS, TIMEOUT, SUSPEND_TIME, RESUME_TIME, ERROR_NUMBER
  2  FROM DBA_RESUMABLE;

SESSION_ID STATUS       TIMEOUT SUSPEND_TIME         RESUME_TIME          ERROR_NUMBER
---------- --------- ---------- -------------------- -------------------- ------------
       120 ABORTED          300                                                   1659

中止會話掛起後，可以從DBA_RESUMABLE看到會話處於ABORTED狀態，導致會話發生掛起的錯誤資訊仍然保留。

而剛才被掛起的會話並不是報錯ORA-1659錯誤，而是操作被中止的錯誤：

CREATE TABLE T (ID NUMBER)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL>

DBMS_RESUMABLE包除了ABORT過程外，還包括SET_TIMEOUT和GET_TIMEOUT等過程：

SQL> SELECT DBMS_RESUMABLE.GET_TIMEOUT FROM DUAL;

GET_TIMEOUT
-----------
         -1

SQL> SELECT DBMS_RESUMABLE.GET_SESSION_TIMEOUT(120) FROM DUAL;

DBMS_RESUMABLE.GET_SESSION_TIMEOUT(120)
---------------------------------------
                                    300

當前會話沒有設定ENABLE RESUMABLE，因此得到的結果是-1，如果要查詢非當前會話的TIMEOUT值，使用GET_SESSION_TIMEOUT函式。

SQL> EXEC DBMS_RESUMABLE.SET_SESSION_TIMEOUT(120, 3600)                                  

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_RESUMABLE.GET_SESSION_TIMEOUT(120) FROM DUAL;

DBMS_RESUMABLE.GET_SESSION_TIMEOUT(120)
---------------------------------------
                                   3600

SQL> SELECT SESSION_ID, STATUS, TIMEOUT, SUSPEND_TIME, RESUME_TIME, ERROR_NUMBER
  2  FROM DBA_RESUMABLE;

SESSION_ID STATUS       TIMEOUT SUSPEND_TIME         RESUME_TIME          ERROR_NUMBER
---------- --------- ---------- -------------------- -------------------- ------------
       120 NORMAL          3600                                                      0

可以看到，設定會話120的TIMEOUT為3600之後，同時會清除會話的狀態資訊，以及ERROR_NUMBER等資訊。

除了在資料庫中的檢視中可以查詢到錯誤資訊，DBMS_RESUMABLE包的SPACE_ERROR_INFO函式，也可以看到錯誤資訊，這個函式會在下一篇文章，配合AFTER SUSPEND觸發器一起介紹。

最後，在資料庫外的alert檔案中，一樣可以看到錯誤資訊：

Tue Jun 16 17:01:48 2009
ORA-1652: unable to extend temp segment by 512 in tablespace                 TEST
Tue Jun 16 17:12:53 2009
statement in resumable session 'User TEST(74), Session 120, Instance 1' was suspended due to
    ORA-01659: unable to allocate MINEXTENTS beyond 24 in tablespace TEST
Tue Jun 16 17:14:34 2009
statement in resumable session 'User TEST(74), Session 120, Instance 1' was aborted