The DBMS_RESUMABLE Package

If the ORA_SPACE_ERROR_INFO function returns FALSE, then the situation causing the suspended statement cannot be fixed. Thus, there is no rational reason for remaining suspended. Unfixable statements can be aborted from within the AFTER_SUSPEND trigger using the ABORT procedure in the DBMS_RESUMABLE package. The following provides an example of issuing this procedure:

/* File on web: local_abort.sql */CREATE OR REPLACE TRIGGER after_suspendAFTER SUSPENDON SCHEMADECLARE CURSOR curs_get_sid IS SELECT sid FROM v$session WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'); v_sid NUMBER; v_error_type VARCHAR2(30);... BEGIN IF ORA_SPACE_ERROR_INFO(......try to fix things... ELSE -- cant fix the situation OPEN curs_get_sid; FETCH curs_get_sid INTO v_sid; CLOSE curs_get_sid; DBMS_RESUMABLE.ABORT(v_sid); END IF; END;

The ABORT procedure takes a single argument, the ID of the session to abort. This allows ABORT to be called from a DATABASE- or SCHEMA-level AFTER SUSPEND trigger. The aborted session receives this error:

ORA-01013: user requested cancel of current operation

After all, the cancellation was requested by a user, but exactly which user is unclear.

In addition to the ABORT procedure, the DBMS_RESUMABLE package contains functions and procedures to get and set timeout values. In the following example, the GET_SESSION_TIMEOUT function returns the timeout value of the suspended session by session ID:

FUNCTION DBMS_RESUMABLE.GET_SESSION_TIMEOUT (sessionid IN NUMBER) RETURN NUMBER;

The SET_SESSION_TIMEOUT function sets the timeout value of the suspended session by session ID:

PROCEDURE DBMS_RESUMABLE.SET_SESSION_TIMEOUT (sessionid IN NUMBER, TIMEOUT IN NUMBER);

The GET_TIMEOUT function returns the timeout value of the current session:

FUNCTION DBMS_RESUMABLE.GET_TIMEOUT RETURN NUMBER;

The SET_SESSION_TIMEOUT function sets the timeout value of the current session:

PROCEDURE DBMS_REUSABLE.SET_TIMEOUT (TIMEOUT IN NUMBER);

New timeout values take effect immediately but do not reset the counter to zero.