Operationen, die unvorhergesehen abbrechen, da sie nicht mehr über ausreichenden Speicherplatz im TEMP, UNDO oder DATA Tablespace verfügen, führen zu erhöhtem Arbeitsaufwand. Sie müssen neu angestartet werden und beanspruchen damit insgesamt mehr Zeit als vielleicht eingeplant war. Besonders lästig ist dies beispielsweise bei langlaufenden Ladevorgängen oder bei langwierigen INDEX CREATE Operationen.

Das Feature RESUMABLE SPACE ALLOCATION, das seit jeher in der Datenbank ohne zusätzliche Installation zur Verfügung steht, schafft Abhilfe. So können Operationen als "resumable" angestartet werden. Bei Speicherplatz-Engpässen wird dann die Operation nicht abgebrochen, sondern es werden Alerts generiert, damit automatisch nach der Fehlerbehebung die Operation fortgeführt werden kann. Die Datenbank selbst verwendet dieses Feature mittlerweile auch intern zum Beispiel beim Data Pump Export und Import. Das Feature steht in jeder Edition zur Verfügung; einzige Voraussetzung ist das "Resumable" Systemprivileg.

Welche Operationen können nun in diesem speziellen Modus angestartet werden bzw. profitieren von diesem Feature? Es handelt sich dabei um:

• SELECTs, die wegen eines TEMP Tablespace Engpass abbrechen
• DML Operationen
• Export/Import und SQL*Loader Operationen
• DDL Operationen wie CREATE INDEX, CREATE TABLE oder ALTER TABLE

1. Ein Statement wird im RESUMABLE Mode angestartet. Dies geschieht durch den SYSTEM/SESSION Parameter RESUMABLE_TIMEOUT oder über die Session-Einstellung ENABLE RESUMABLE
2. Ein RESUMABLE Statement wird eine definierte Zeitdauer (TIMEOUT-Wert) angehalten (auch suspended), wenn eine der folgenden Bedingungen eintritt:
   • Out of Space Bedingung
   • Maximum Extents Bedingung
   • Space Quota
3. Die eingetretenen Fehler und der Zustand SUSPEND werden in speziellen Views wie DBA_RESUMABLE, V$SESSION_WAIT, DBA_OUTSTANDING_ALERTS und der Alert-Datei angezeigt. Zusätzlich existiert ein AFTER SUSPEND Trigger Typ, der automatisch auf das Speicherplatz-Problem reagieren kann.
4. Nach Lösung des Problems wird die gestoppte Operation weitergeführt, und das Alert "Resumable Session Suspended" wird gelöscht.

SQL> GRANT resumable TO u;
SQL> connect u/
Connected.
SQL> ALTER SESSION ENABLE RESUMABLE timeout 3600;
Session altered.

--Als User SYS die Aktion in DBA_RESUMABLE überwachen:
SQL> SELECT user_id, session_id,  status, timeout, start_time FROM dba_resumable;

  USER_ID SESSION_ID STATUS       TIMEOUT START_TIME
---------- ---------- --------- ---------- --------------------
       162         79 NORMAL          3600

-- Aktionen als User U um Platz zu verbrauchen 
SQL> INSERT INTO t  SELECT * FROM t;
...

Tue Dec 06 10:03:55 2011
statement in resumable session 'User U(162), Session 79, Instance 1' was suspended due to
    ORA-01536: space quota exceeded for tablespace 'USERS'

SQL> SELECT status, start_time, suspend_time, resume_time, sql_text, error_msg
     FROM dba_resumable WHERE session_id=79;

STATUS    START_TIME        SUSPEND_TIME      RESUME_TIME SQL_TEXT                      
--------- ----------------- ----------------- ----------- ----------------------------- 
ERROR_MSG
------------------------------------------------------
SUSPENDED 12/06/11 10:03:52 12/06/11 10:03:55             insert into t select * from t 
ORA-01536: space quota exceeded for tablespace 'USERS'

SQL> SELECT event, state, wait_time FROM v$session_wait WHERE sid=79;

EVENT
----------------------------------------------------------------
STATE                WAIT_TIME
------------------- ----------
statement suspended, wait error to be cleared
WAITING                      0

SQL> SELECT decode(message_level, 5, 'WARNING', 1, 'CRITICAL') alert_level, reason
     FROM dba_outstanding_alerts WHERE reason LIKE '%resumable%';

ALERT_LEVEL         REASON
------------------- --------------------------------------------------------------------------------
WARNING             Operation on resumable session User U(162), Session 79, Instance 1 session id 79
                    suspended because of errors in user quota. Error message is ORA-01536: space qu
                    ota exceeded for tablespace 'USERS'

CREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND
ON DATABASE
DECLARE
   cur_sid           NUMBER;
   cur_inst          NUMBER;
   errno             NUMBER;
   err_type          VARCHAR2(1000);
   object_owner      VARCHAR2(1000);
   object_type       VARCHAR2(1000);
   ts_name           VARCHAR2(1000);
   object_name       VARCHAR2(1000);
   sub_object_name   VARCHAR2(1000);
   error_txt         VARCHAR2(1000);
   msg               VARCHAR2(1000);
   ret_value         BOOLEAN;
BEGIN
  -- Session ID und Instance Number finden
  SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;
  cur_inst := userenv('instance');
  -- Fehlerinformation abfragen
  ret_value := 
  DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,ts_name,object_name,sub_object_name);
  -- Abfrage auf Fehlertyp
  IF err_type = 'SPACE QUOTA EXCEEDED' THEN      
     SELECT ERROR_MSG INTO error_txt FROM DBA_RESUMABLE 
     WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;
  -- Mögliche Aktionen: Nachricht in Hilfstabelle schreiben, Email schreiben, Aktion abbrechen
     msg:='Subject: Space Error Occurred for segment ' || object_name ||' am 
          '|| TO_CHAR(SYSDATE,'Monat dd, YYYY, HH:MI am')||'. Fehlermeldung:'||error_txt;
     ...        
     DBMS_RESUMABLE.ABORT(cur_sid);    
   ELSE
    ...
   END IF;
END;
/

• Oracle Database Utilities 11g Release 2 (11.2) Data Pump Legacy Mode
• Oracle Database Utilities 11g Release 2 (11.2) SQL*Loader Command-Line Reference
• Package DBMS_RESUMABLE
• Oracle Database Administrator's Guide 11g Release 2 (11.2) Managing Resumable Space Allocation

Zurück zur Community-Seite