Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E25788-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub
ARPLS143

122 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:

ARPLS67667

Using DBMS_RESUMABLE

ARPLS67668

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.

ARPLS67669

Summary of DBMS_RESUMABLE Subprograms

ARPLS67670Table 122-1 DBMS_RESUMABLE Package Subprograms

Subprogram Description

ABORT Procedure

Aborts a suspended resumable space allocation

GET_SESSION_TIMEOUT Function

Returns the current timeout value of the resumable space allocations for a session with session_id

GET_TIMEOUT Function

Returns the current timeout value of resumable space allocations for the current session

SET_SESSION_TIMEOUT Procedure

Sets the timeout of resumable space allocations for a session with session_id

SET_TIMEOUT Procedure

Sets the timeout of resumable space allocations for the current session

SPACE_ERROR_INFO Function

Looks for space-related errors in the error stack, otherwise returning FALSE


ARPLS67671

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

ARPLS67672Table 122-2 ABORT Procedure Parameters

Parameter Description

session_id

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.

ARPLS67673

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

ARPLS67674Table 122-3 GET_SESSION_TIMEOUT Function Parameters

Parameter Description

session_id

The session identifier of the resumable space allocation.


Return Values

ARPLS67675Table 122-4 GET_SESSION_TIMEOUT Function Return Values

Return Value Description

NUMBER

The current timeout value of resumable space allocations for a session with session_id.The timeout is returned in seconds.


Usage Notes

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

ARPLS67676

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

ARPLS67677Table 122-5 GET_TIMEOUT Function Return Values

Return Value Description

NUMBER

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.

ARPLS67678

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

ARPLS67679Table 122-6 SET_SESSION_TIMEOUT Procedure Parameters

Parameter Description

session_id

The session identifier of the resumable space allocation.

timeout

The timeout of the resumable space allocation.


ARPLS67680

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

ARPLS67681Table 122-7 SET_TIMEOUT Procedure Parameters

Parameter Description

timeout

The timeout of the resumable space allocation.


ARPLS67682

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

ARPLS67683Table 122-8 SPACE_ERROR_INFO Function Parameters

Parameter Description

error_type

The space error type. It will be one of the following:

  • NO MORE SPACE

  • MAX EXTENTS REACHED

  • SPACE QUOTA EXCEEDED

object_type

The object type. It will be one of the following:

  • TABLE

  • INDEX

  • CLUSTER

  • TABLE SPACE

  • ROLLBACK SEGMENT

  • UNDO SEGMENT

  • LOB SEGMENT

  • TEMP SEGMENT

  • INDEX PARTITION

  • TABLE PARTITION

  • LOB PARTITION

  • TABLE SUBPARTITION

  • INDEX SUBPARTITION

  • LOB SUBPARTITION

The type can also be NULL if it does not apply.

object_owner

The owner of the object. NULL if it cannot be determined.

table_space_name

The table space where the object resides. NULL if it cannot be determined.

object_name

The name of rollback segment, temp segment, table, index, or cluster.

sub_object_name

The partition name or sub-partition name of LOB, TABLE, or INDEX. NULL if it cannot be determined.


Reader Comment

   

Comments, corrections, and suggestions are forwarded to authors every week. By submitting, you confirm you agree to the terms and conditions. Use the OTN forums for product questions. For support or consulting, file a service request through My Oracle Support.

Hide Navigation

Quick Lookup

Database Library · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Initialization Parameters · Advanced Search · Error Messages

Main Categories

This Page

This Document

New and changed documents:
RSS Feed HTML RSS Feed PDF