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.
Suspending a statement automatically results in suspending the transaction. Thus all
transactional resources are held through a statement suspend and resume. 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.