You have enabled resumable space allocation in your database by setting the
RESUMABLE_TIMEOUT parameter set to a nonzero value.
Which three statements about resumable space allocation are true? (Choose three.)
A.
Even with resumable space allocation enabled for your database, you can disable resumable
space allocation for a single session.
B.
A resumable statement is suspended only if an out of space error occurs.
C.
When a resumable statement is suspended, the transaction that contains the statement is also
suspended.
D.
A resumable statement can only be suspended and resumed once during the execution of the
statement.
E.
You can query the V$SESSION_WAIT dynamic performance view to identify the statements
that are suspended for a session.
Explanation:
The following three statements are true about resumable space allocation:
Even with resumable space allocation enabled for your database, you can disable resumable
space allocation for a single session. When a resumable statement is suspended, the transaction
that contains the statement is also suspended. You can query the V$SESSION_WAIT dynamic
performance view to identify the statements that are suspended for a session. Resumable space
allocation is an Oracle feature that allows certain statements to be suspended if space errors,
such as out of space errors, space quota exceeded errors, or maximum extents reached errors,
occur. Resumable statements include SELECT statements, DML statements, SQL*Loader imports
and exports, and some DDL statements. You enable resumable space allocation by setting the
RESUMABLE_TIMEOUT parameter to a value greater than zero. The parameter represents the
amount of time, in seconds, that a resumable statement will be suspended before it is terminated
and an error is generated. You can also set this value for a session using the ALTER SESSION
ENABLE RESUMABLE TIMEOUT n; statement, or, you can disable resumable space allocation
for a session using the ALTER SESSION DISABLE RESUMABLE; statement. If you have enabled
resumable space allocation and a resumable statement experiences a space error, the statement
is suspended. If the statement is contained within a transaction, the transaction is also suspended.
This gives you the opportunity to correct the space issue. After the space issue is corrected, the
suspended statement automatically resumes execution.
You can query the V $SESSION_WAIT dynamic performance view to identify the statements that
are suspended for a session. The event column of the V$SESSION_WAIT view will display
statement suspended, wait error to be cleared for a statement that is suspended. The sid column
identifies the session id that executed the suspended statement, and the seconds_in_wait column
displays the number of seconds the statement has been suspended. The option that states a
resumable statement is suspended only if an out of space error occurs is incorrect. A resumable
statement is also suspended if a space quota is exceeded or a maximum extents reached error
occurs. The option that states a resumable statement can only be suspended and resumed once
during the execution of the statement is incorrect. A statement can be suspended and resumed
multiple times during its execution.