空間RESUMABLE操作（三）

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

這一篇簡單介紹空間RESUMABLE的AFTER SUSPEND觸發器。

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

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

 

 

前面介紹了Oracle的RESUMABLE特性，不過RESUMABLE特性有一個顯而易見的缺點，就是沒有任何的資訊返回給使用者。如果使用者沒有意識到問題，就會一直處於的等待狀態中。

Oracle提供了一個AFTER SUSPEND觸發器，對解決這種情況有一些幫助。

SQL> CONN / AS SYSDBA
已連線。
SQL> CREATE OR REPLACE TRIGGER TRI_AFTER_SUSPEND
  2  AFTER SUSPEND ON DATABASE
  3  DECLARE
  4   PRAGMA AUTONOMOUS_TRANSACTION;
  5   V_RESULT BOOLEAN;
  6   V_ERROR_TYPE VARCHAR2(32767);
  7   V_OBJECT_TYPE VARCHAR2(32767);
  8   V_OBJECT_OWNER VARCHAR2(30);
  9   V_TABLESPACE_NAME VARCHAR2(30);
 10   V_OBJECT_NAME VARCHAR2(128);
 11   V_SUB_OBJECT_NAME VARCHAR2(128);
 12   V_CONNECT UTL_SMTP.CONNECTION;
 13   V_RECEIVE VARCHAR2(100) := 'yangtk@cis.com.cn';
 14   V_SERVER VARCHAR2(100) := 'mail.itpub.net';
 15   V_SENDER VARCHAR2(100) := 'yangtingkun@itpub.net';
 16   V_PASSWORD VARCHAR2(100) := 'password';
 17  BEGIN
 18   V_RESULT := DBMS_RESUMABLE.SPACE_ERROR_INFO(
 19    V_ERROR_TYPE,
 20    V_OBJECT_TYPE,
 21    V_OBJECT_OWNER,
 22    V_TABLESPACE_NAME,
 23    V_OBJECT_NAME,
 24    V_SUB_OBJECT_NAME);
 25   V_CONNECT := UTL_SMTP.OPEN_CONNECTION(V_SERVER, 25);
 26   UTL_SMTP.HELO(V_CONNECT, V_SERVER);
 27   UTL_SMTP.COMMAND(V_CONNECT, 'AUTH LOGIN');
 28   UTL_SMTP.COMMAND
 29   (
 30    V_CONNECT,
 31    UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(V_SENDER)))
 32   );
 33   UTL_SMTP.COMMAND
 34   (
 35    V_CONNECT,
 36    UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(V_PASSWORD)))
 37   );
 38   UTL_SMTP.MAIL(V_CONNECT, V_SENDER);
 39   UTL_SMTP.RCPT(V_CONNECT, V_RECEIVE);
 40   UTL_SMTP.DATA
 41   (
 42    V_CONNECT,
 43    'Suspend errors! ' || V_OBJECT_TYPE || ' ' || V_OBJECT_OWNER || '.' || V_OBJECT_NAME
 44     || ' on tablespace ' || V_TABLESPACE_NAME || ' is suspend, error is ' || V_ERROR_TYPE
 45   );
 46   UTL_SMTP.QUIT(V_CONNECT);
 47  END;
 48  /

觸發器已建立

利用這個觸發器的功能，可以在發生SUSPEND後，向指定的郵箱傳送郵件，這樣就可以避免使用者沒有意識到SUSPEND的產生，而一直處於等待的情況。

SQL> CONN YANGTK/YANGTK
已連線。
SQL> SELECT SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE
  2  WHERE TABLESPACE_NAME = 'YANGTK';

SUM(BYTES)/1024/1024
--------------------
            214.4375

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

會話已更改。

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

當前使用者進入SUSPEND狀態後，使用者可以收到相應的郵件通知。

 

郵件內容如下：

Suspend errors!  . on tablespace YANGTK is suspend, error is NO MORE SPACE

由於是建立物件過程報錯，因此物件本身還沒有建立成功，沒有取到OWNER和OBJECT_NAME是正常的現象，不過即使沒有OWNER和OBJECT_NAME的資訊，這個郵件也足以說明當前碰到的錯誤了。