OCP-043 When the execution of a resumable statement is suspended

41. In which two conditions are resumable statements suspended? (Choose two.)
A.when a user session is terminated
B.when a user exceeds the space quota
C.when a user manually suspends the statement
D.when the maximum extents of a segment is reached
E.when a table that is being accessed by the current transaction is not found
Answer: BD

Resumable Space Allocation Overview

This section provides an overview of resumable space allocation. It describes how resumable space allocation works, and specifically defines qualifying statements and error conditions.

How Resumable Space Allocation Works

The following is an overview of how resumable space allocation works. Details are contained in later sections.

1. A statement executes in a resumable mode only if its session has been enabled for resumable space allocation by one of the following actions:

   • The RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.

   • The ALTER SESSION ENABLE RESUMABLE statement is issued.

2. A resumable statement is suspended when one of the following conditions occur (these conditions result in corresponding errors being signalled for non-resumable statements):

   • Out of space condition

   • Maximum extents reached condition

   • Space quota exceeded condition.

3. When the execution of a resumable statement is suspended, there are mechanisms to perform user supplied operations, log errors, and to query the status of the statement execution. When a resumable statement is suspended the following actions are taken:

   • The error is reported in the alert log.

   • The system issues the Resumable Session Suspended alert.

   • If the user registered a trigger on the AFTER SUSPEND system event, the user trigger is executed. A user supplied PL/SQL procedure can access the error message data using the DBMS_RESUMABLE package and the DBA_ or USER_RESUMABLE view.

4. Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held through a statement suspend and resume.

5. When the error condition is resolved (for example, as a result of user intervention or perhaps sort space released by other queries), the suspended statement automatically resumes execution and the Resumable Session Suspended alert is cleared.

6. A suspended statement can be forced to throw the exception using the DBMS_RESUMABLE.ABORT() procedure. This procedure can be called by a DBA, or by the user who issued the statement.

7. A suspension time out interval is associated with resumable statements. A resumable statement that is suspended for the timeout interval (the default is two hours) wakes up and returns the exception to the user.

8. A resumable statement can be suspended and resumed multiple times during execution.

What Operations are Resumable?

The following operations are resumable:

• Queries

  SELECT statements that run out of temporary space (for sort areas) are candidates for resumable execution. When using OCI, the calls OCIStmtExecute()and OCIStmtFetch() are candidates.

• DML

  INSERT, UPDATE, and DELETE statements are candidates. The interface used to execute them does not matter; it can be OCI, SQLJ, PL/SQL, or another interface. Also, INSERT INTO...SELECT from external tables can be resumable.

• Import/Export

  As for SQL*Loader, a command line parameter controls whether statements are resumable after recoverable errors.

• DDL

  The following statements are candidates for resumable execution:

  • CREATE TABLE ... AS SELECT

  • CREATE INDEX

  • ALTER INDEX ... REBUILD

  • ALTER TABLE ... MOVE PARTITION

  • ALTER TABLE ... SPLIT PARTITION

  • ALTER INDEX ... REBUILD PARTITION

  • ALTER