נניח שיש לנו טרנזאקציה של פקודת insert ארוכה במיוחד (כזו שלוקחת שעות) ובאמצע ה-insert מרפי קופץ לביקור ונגמר המקום ב-tablespace של הטבלה. על פניו, אירוע מצער שאי אפשר להתגבר אליו אלא להתחיל מחדש, נכון? אז זהו שלא. ישנו פיצ'ר בשם Resumable Space Allocation שמאפשר לנו להכניס את ה-session להשהייה ולהמתין פרק זמן מוגדר כדי שנוכל לטפל בבעיה. ברגע שנטפל בבעיה, ה-session שלנו ימשיך כרגיל באופן אוטומטי, כאילו לא קרה שום דבר.

זה לא פיצ'ר חדש במיוחד – למעשה הוא קיים כבר מגרסה 9i – אבל הופתעתי היום לגלות שיש מפתחים (ויותר גרוע, DBA-ים) שפשוט לא מכירים את זה. בוא נראה איך אפשר להשתמש בכלי החשוב הזה.

שימוש

אז במה בעצם מדובר? כמו שכתבתי קודם, הכלי הזה מאפשר לנו להגן על פקודות DML (כלומר insert,update,delete, merge), ריצות של SQL Loader ואפילו על כמה פקודות DDL שיכולות לקחת זמן רב ולהיכשל בגלל בעיות של מקום ב-tablespace, כמות Extent-ים או בגלל בעיית הקצאות (quota על tablespace). מכוון שלפעמים אנחנו מעדיפים שהפקודה לא תיכשל לגמרי, אנחנו נוכל להכניס אותה למצב השהייה (suspend), לטפל בבעיה מבלי להריץ את הפקודה שוב ושוב וכאשר נסיים בטיפול, היא תמשיך באופן עצמאי כאילו לא קרה דבר. את הכלי הזה אפשר להפעיל הן ברמת ה-session או ברמת כל ה-database.

כדי להפעיל את ה-Resumable Space Allocation ברמת ה-session יש להשתמש בפקודות הבאות:

ALTER SESSION ENABLE RESUMABLE;
ALTER SESSION DISABLE RESUMABLE;

באופן אישי אני ממליץ להפעיל את ה-resumable עם timeout מוגדר מראש ועם שם, כדי לזהות את הפעולה לאחר מכן:

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'Zohar insert into table';

כאשר תהיה בעיה, ניתן יהיה לזהות שנכנסנו למצב suspend על ידי התבוננות ב-alert log או על ידי שליפה.
ב-alert log זה נראה ככה:

Mon Dec  5 22:03:18 2010
statement in resumable session 'User ZELKAYAM2(4883), Session 913, Instance 1' was suspended due to
    ORA-01653: unable to extend table ZELKAYAM2.MY_OBJECTS by 64 in tablespace TBS_RESUMABLE_TEST

כדי לזהות מהם ה-session-ים שנמצאים כרגע ב-Suspend על ידי שליפה ניתן להשתמש ב-View-ים של USER/DBA_RESUMABLE או בשליפה השימושית הבאה שכבר מפרמטת את התוצאה למשהו קריא יותר:

select du.username usera,
       '(' || COORD_SESSION_ID || ')' || session_id as sess,
       start_time,
       suspend_time,
       round((timeout / 60)) || ':' || mod(timeout, 60) as timeout,
       round(extract(hour   from(sysdate - to_date(suspend_time, 'mm/dd/yy hh24:mi:ss')) DAY TO SECOND)) || ' hour ' ||
       round(extract(minute from(sysdate - to_date(suspend_time, 'mm/dd/yy hh24:mi:ss')) DAY TO SECOND)) || ' min ' ||
       round(extract(second from(sysdate - to_date(suspend_time, 'mm/dd/yy hh24:mi:ss')) DAY TO SECOND)) || ' sec' as suspend_duration,
       round((timeout / 60) - (sysdate - to_date(suspend_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60) minutes_left
  from dba_resumable dr, dba_users du
 where dr.user_id = du.user_id
   and dr.status <> 'NORMAL'
 order by minutes_left;

כדי "לדוג" רק את השמות של ה-Tablespace-ים שצריכים לטפל בהם ניתן להשתמש בשליפה הבאה:

SELECT DISTINCT CASE
                    WHEN error_msg LIKE '%in tablespace%' THEN
                     TRIM(SUBSTR(dr.error_msg, INSTR(dr.error_msg, ' ', -1)))
                    ELSE
                     'unknown'
                END susspend_tablespace
  FROM dba_resumable dr
 WHERE status != 'NORMAL';

תפעול

כדי להגדיל את הזמן של ה-timeout של session שנמצא ב-resumable mode ניתן להשתמש ב-DBMS_RESUMABLE (גם אם הוא כרגע ב-suspend):

begin
  sys.dbms_resumable.set_session_timeout(sessionid => 913,
                                         timeout => 3600);
end;
/

כדי להכשיל את הפקודה שממתינה ל-resume בלי להרוג את ה-session ומבלי להמתין לסוף ה-timeout (הפקודה תקבל את ההודעה של החוסר במקום), ניתן להשתמש בפקודה הבאה:

begin
  sys.dbms_resumable.abort(sessionid => 913);
end;
/

למתקדמים (ו-DBA-ים): ניתן להגדיר ON DATABASE trigger שירוץ AFTER SUSPEND ויתריע או יטפל בצורה אוטומטית מיד כאשר Suspend קורה.

מגבלות

• פקודות שרצות ב-remote (דרך dblink) לא ניתן להגדיר כ-resumable.
• יכולים להיות מקרים בהם חלק מהפרוססים הפרללים יכשלו וחלקם ימשיכו לרוץ. במקרה כזה אם נבטל (abort) את אחד הפרוססים הפרללים, זה יכשיל את כל הפקודה.
• במידה ויש לנו tablespace שמוגדר בניהול על ידי dictionary והאובייקט מגיע ל-maxextents – לא יתבצע suspend מכוון שלא ניתן יהיה לטפל בבעיה on-the-fly אלא רק על ידי שינוי הטבלה.

כרגיל, אם יש לכם הערות או תוספות, אני אשמח לשמוע.

אני אוסיף בהזדמנות את רשימת קודי השגיאה שאני מכיר שיכולים לגרום ל-suspend.