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:
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.
Table 87-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 |
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.
DBMS_RESUMABLE.ABORT ( session_id IN NUMBER);
Table 87-2 ABORT Procedure Parameters
Parameter | Description |
---|---|
|
The session identifier of the resumable space allocation. |
To call an ABORT
procedure, you must be the owner of the session with session_id,
have ALTER SYSTEM
privileges, or be a DBA.
This function returns the current timeout value of resumable space allocations for a session with session_id.
DBMS_RESUMABLE.GET_SESSION_TIMEOUT ( session_id IN NUMBER) RETURN NUMBER;
Table 87-3 GET_SESSION_TIMEOUT Function Parameters
Parameter | Description |
---|---|
|
The session identifier of the resumable space allocation. |
Table 87-4 GET_SESSION_TIMEOUT Function Return Values
Return Value | Description |
---|---|
|
The current timeout value of resumable space allocations for a session with |
If session_id
does not exist, the GET_SESSION_TIMEOUT
function returns -1.
This function returns the current timeout value of resumable space allocations for the current session.
DBMS_RESUMABLE.GET_TIMEOUT RETURN NUMBER;
Table 87-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. |
If the current session is not resumable enabled, the GET_TIMEOUT
function returns -1.
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.
DBMS_RESUMABLE.SET_SESSION_TIMEOUT ( session_id IN NUMBER, timeout IN NUMBER);
Table 87-6 SET_SESSION_TIMEOUT Procedure Parameters
Parameter | Description |
---|---|
|
The session identifier of the resumable space allocation. |
|
The timeout of the resumable space allocation. |
This procedure sets the timeout of resumable space allocations for the current session. The new timeout setting applies to the session immediately.
DBMS_RESUMABLE.SET_TIMEOUT ( timeout IN NUMBER);
Table 87-7 SET_TIMEOUT Procedure Parameters
Parameter | Description |
---|---|
|
The timeout of the resumable space allocation. |
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.
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;
Table 87-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 |