139 DBMS_RESUMABLE
With the DBMS_RESUMABLE package, you can suspend large operations that run out of space or reach space limits after executing for a long time, fix the problem, and make the statement resume execution. In this way you can write applications without worrying about running into space-related errors.
This chapter contains the following topics:
139.1 DBMS_RESUMABLE 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 DBMS_RESUMABLE.ABORT() procedure.
139.2 Summary of DBMS_RESUMABLE Subprograms
This table lists the DBMS_RESUMABLE subprograms and briefly describes them.
Table 139-1 DBMS_RESUMABLE Package Subprograms
| Subprogram | Description |
|---|---|
|
Aborts a suspended resumable space allocation |
|
|
Returns the current timeout value of the resumable space allocations for a session with |
|
|
Returns the current timeout value of resumable space allocations for the current session |
|
|
Sets the timeout of resumable space allocations for a session with |
|
|
Sets the timeout of resumable space allocations for the current session |
|
|
Looks for space-related errors in the error stack, otherwise returning |
139.2.1 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
Table 139-2 ABORT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The session identifier of the resumable space allocation. |
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.
139.2.2 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
Table 139-3 GET_SESSION_TIMEOUT Function Parameters
| Parameter | Description |
|---|---|
|
|
The session identifier of the resumable space allocation. |
Return Values
Table 139-4 GET_SESSION_TIMEOUT Function Return Values
| Return Value | Description |
|---|---|
|
|
The current timeout value of resumable space allocations for a session with |
Usage Notes
If session_id does not exist, the GET_SESSION_TIMEOUT function returns -1.
139.2.3 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
Table 139-5 GET_TIMEOUT Function Return Values
| Return Value | Description |
|---|---|
|
|
The current timeout value of resumable space allocations for the current session. The returned value is in seconds. |
Usage Notes
If the current session is not resumable enabled, the GET_TIMEOUT function returns -1.
139.2.4 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
Table 139-6 SET_SESSION_TIMEOUT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The session identifier of the resumable space allocation. |
|
|
The timeout of the resumable space allocation. |
139.2.5 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
Table 139-7 SET_TIMEOUT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The timeout of the resumable space allocation. |
139.2.6 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
Table 139-8 SPACE_ERROR_INFO Function Parameters
| Parameter | Description |
|---|---|
|
|
The space error type. It will be one of the following:
|
|
|
The object type. It will be one of the following:
The type can also be |
|
|
The owner of the object. |
|
|
The table space where the object resides. |
|
|
The name of rollback segment, temp segment, table, index, or cluster. |
|
|
The partition name or sub-partition name of |