87 DBMS_RESUMABLE

Using DBMS_RESUMABLE

• Operational Notes

---

Operational Notes

When you suspend a statement, you should log the suspension in the alert log. You should also register a procedure to be executed when the statement is suspended. Using a view, you can monitor the progress of the statement and indicate whether the statement is currently executing or suspended.

Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held during a statement suspend and resume. When the error condition disappears, the suspended statement automatically resumes execution. A resumable space allocation can be suspended and resumed multiple times during execution.

A suspension timeout interval is associated with resumable space allocations. A resumable space allocation that is suspended for the timeout interval (the default is two hours) wakes up and returns an exception to the user. A suspended statement may be forced to throw an exception using the DMBS_RESUMABLE.ABORT() procedure.

Summary of DBMS_RESUMABLE Subprograms

---

ABORT Procedure

This procedure aborts a suspended resumable space allocation. The parameter session_id is the session ID in which the statement is executed. For a parallel DML/DDL, session_id is any session ID that participates in the parallel DML/DDL. This operation is guaranteed to succeed. The procedure can be called either inside or outside of the AFTER SUSPEND trigger.

Syntax

DBMS_RESUMABLE.ABORT (
   session_id  IN NUMBER);

Parameters

Usage Notes

To call an ABORT procedure, you must be the owner of the session with session_id, have ALTER SYSTEM privileges, or be a DBA.

---

GET_SESSION_TIMEOUT Function

This function returns the current timeout value of resumable space allocations for a session with session_id.

Syntax

DBMS_RESUMABLE.GET_SESSION_TIMEOUT (
   session_id  IN NUMBER)
RETURN NUMBER;

Parameters

Return Values

Usage Notes

If session_id does not exist, the GET_SESSION_TIMEOUT function returns -1.

---

GET_TIMEOUT Function

This function returns the current timeout value of resumable space allocations for the current session.

Syntax

DBMS_RESUMABLE.GET_TIMEOUT
 RETURN NUMBER;

Return Values

Usage Notes

If the current session is not resumable enabled, the GET_TIMEOUT function returns -1.

---

SET_SESSION_TIMEOUT Procedure

This procedure sets the timeout of resumable space allocations for a session with session_id. The new timeout setting applies to the session immediately. If session_id does not exist, no operation occurs.

Syntax

DBMS_RESUMABLE.SET_SESSION_TIMEOUT (
   session_id  IN NUMBER,
   timeout     IN NUMBER);

Parameters

---

SET_TIMEOUT Procedure

This procedure sets the timeout of resumable space allocations for the current session. The new timeout setting applies to the session immediately.

Syntax

DBMS_RESUMABLE.SET_TIMEOUT (
   timeout  IN NUMBER);

Parameters

---

SPACE_ERROR_INFO Function

This function looks for space-related errors in the error stack. If it cannot find a space related error, it will return FALSE. Otherwise, TRUE is returned and information about the particular object that causes the space error is returned.

Syntax

DBMS_RESUMABLE.SPACE_ERROR_INFO
   error_type        OUT VARCHAR2, 
   object_type       OUT VARCHAR2, 
   object_owner      OUT VARCHAR2, 
   table_space_name  OUT VARCHAR2, 
   object_name       OUT VARCHAR2, 
   sub_object_name   OUT VARCHAR2) 
RETURN BOOLEAN;

Parameters