A user receives the following error while performing a large volume of inserts into a table:
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘USERS’
The issue is resolved by increasing the space quota on the USERS tablespace for the user. But the user may
perform such transaction in the future. You want to ensure that the command waits rather than produce an
error when such an event occurs the next time.
What can you do to achieve this before running the command in the future?
A.
Set RESUMABLE_TIMEOUT for the instance.
B.
Set the RESOURCE_LIMIT Parameter to TRUE.
C.
Enable the database instance to use asynchronous commit.
D.
Set the LOG_CHECKPOINT_TIMEOUT parameter to a nonzero value for the database instance.
Explanation:
How Resumable Space Allocation Works
The following is an overview of how resumable space allocation works. Details are contained in later sections.
A statement executes in resumable mode only if its session has been enabled for resumable space allocation
by one of the following actions:
The ALTER SESSION ENABLE RESUMABLE statement is issued in the session before the statement
executes when the RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value. The ALTER
SESSION ENABLE RESUMABLE TIMEOUT timeout_value statement is issued in the session before the
statement executes, and thetimeout_value is a nonzero value.
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.
When the execution of a resumable statement is suspended, there are mechanisms to perform user supplied
operations, log errors, and 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.
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.
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.
A suspension time out interval, specified by the RESUMABLE_TIMEOUT initialization parameter or by the
timeout value in the ALTER SESSION ENABLE RESUMABLETIMEOUT statement, is associated with
resumable statements. A resumable statement that is suspended for the timeout interval wakes up and returns
the exception to the user if the error condition is not resolved within the timeout interval.
A resumable statement can be suspended and resumed multiple times during execution.