Oracle Database provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables you to take corrective action instead of the Oracle Database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The statements that are affected are called resumable statements.
This section contains the following topics:
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.
What Operations are Resumable?
The following operations are resumable:
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.
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.
As for SQL*Loader, a command line parameter controls whether statements are resumable after recoverable errors.
The following statements are candidates for resumable execution:
What Errors are Correctable?
There are three classes of correctable errors:
The operation cannot acquire any more extents for a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition in a tablespace. For example, the following errors fall in this category:
The number of extents in a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition equals the maximum extents defined on the object. For example, the following errors fall in this category:
The user has exceeded his assigned space quota in the tablespace. Specifically, this is noted by the following error:ORA-1536 space quote exceeded for tablespace string
Resumable Space Allocation Limitations for Dictionary-Managed Tablespaces
There are certain limitations of resumable space allocation when using dictionarymanaged tablespaces:
Resumable Space Allocation and Distributed Operations
In a distributed environment, if a user enables or disables resumable space allocation, or if you, as a DBA, alter the RESUMABLE_TIMEOUT initialization parameter, only the local instance is affected. In a distributed transaction, sessions or remote instances are suspended only if RESUMABLE has been enabled in the remote instance.
Parallel Execution and Resumable Space Allocation
In parallel execution, if one of the parallel execution server processes encounters a correctable error, that server process suspends its execution. Other parallel execution server processes will continue executing their respective tasks, until either they encounter an error or are blocked (directly or indirectly) by the suspended server process. When the correctable error is resolved, the suspended process resumes execution and the parallel operation continues execution. If the suspended operation is terminated, the parallel operation aborts, throwing the error to the user.
Different parallel execution server processes may encounter one or more correctable errors. This may result in firing an AFTER SUSPEND trigger multiple times, in parallel. Also, if a parallel execution server process encounters a noncorrectable error while another parallel execution server process is suspended, the suspended statement is immediately aborted.
For parallel execution, every parallel execution coordinator and server process has its own entry in the DBA_ or USER_RESUMABLE view.
Enabling and Disabling Resumable Space Allocation
Resumable space allocation is only possible when statements are executed within a session that has resumable mode enabled. There are two means of enabling and disabling resumable space allocation. You can control it at the system level with the RESUMABLE _TIMEOUT initialization parameter, or users can enable it at the session level using clauses of the ALTER SESSION statement.
Setting the RESUMABLE_TIMEOUT Initialization Parameter
You can enable resumable space allocation system wide and specify a timeout interval by setting the RESUMABLE_TIMEOUT initialization parameter. For example, the following setting of the RESUMABLE_TIMEOUT parameter in the initialization parameter file causes all sessions to initially be enabled for resumable space allocation and sets the timeout period to 1 hour:RESUMABLE_TIMEOUT = 3600
If this parameter is set to 0, then resumable space allocation is disabled initially for all sessions. This is the default.
You can use the ALTER SYSTEM SET statement to change the value of this parameter at the system level. For example, the following statement will disable resumable space allocation for all sessions:ALTER SYSTEM SET RESUMABLE_TIMEOUT=0;
Within a session, a user can issue the ALTER SESSION SET statement to set the RESUMABLE _TIMEOUT initialization parameter and enable resumable space allocation, change a timeout value, or to disable resumable mode.
Using ALTER SESSION to Enable and Disable Resumable Space Allocation
A user can enable resumable mode for a session, using the following SQL statement:ALTER SESSION ENABLE RESUMABLE;
To disable resumable mode, a user issues the following statement:ALTER SESSION DISABLE RESUMABLE;
The default for a new session is resumable mode disabled, unless the RESUMABLE _TIMEOUT initialization parameter is set to a nonzero value.
The user can also specify a timeout interval, and can provide a name used to identify a resumable statement. These are discussed separately in following sections.
Specifying a Timeout Interval A timeout period, after which a suspended statement will error if no intervention has taken place, can be specified when resumable mode is enabled. The following statement specifies that resumable transactions will time out and error after 3600 seconds:ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
The value of TIMEOUT remains in effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement, it is changed by another means, or the session ends. The default timeout interval when using the ENABLE RESUMABLE TIMEOUT clause to enable resumable mode is 7200 seconds.
Naming Resumable Statements Resumable statements can be identified by name. The following statement assigns a name to resumable statements:ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'insert into table';
The NAME value remains in effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement, or the session ends. The default value for NAME is 'User username(userid), Session sessionid, Instance instanceid'.
The name of the statement is used to identify the resumable statement in the DBA_RESUMABLE and USER_RESUMABLE views.
Using a LOGON Trigger to Set Default Resumable Mode
Another method of setting default resumable mode, other than setting the RESUMABLE_TIMEOUT initialization parameter, is that you can register a database level LOGON trigger to alter a user's session to enable resumable and set a timeout interval.
Detecting Suspended Statements
When a resumable statement is suspended, the error is not raised to the client. In order for corrective action to be taken, Oracle Database provides alternative methods for notifying users of the error and for providing information about the circumstances.
Notifying Users: The AFTER SUSPEND System Event and Trigger
When a resumable statement encounter a correctable error, the system internally generates the AFTER SUSPEND system event. Users can register triggers for this event at both the database and schema level. If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended.
SQL statements executed within a AFTER SUSPEND trigger are always nonresumable and are always autonomous. Transactions started within the trigger use the SYSTEM rollback segment. These conditions are imposed to overcome deadlocks and reduce the chance of the trigger experiencing the same error condition as the statement.
Users can use the USER _RESUMABLE or DBA _RESUMABLE views, or the DBMS _ RESUMABLE.SPACE _ERROR _INFO function, within triggers to get information about the resumable statements.
Triggers can also call the DBMS_RESUMABLE package to terminate suspended statements and modify resumable timeout values. In the following example, the default system timeout is changed by creating a system wide AFTER SUSPEND trigger that calls DBMS_RESUMABLE to set the timeout to 3 hours:
Using Views to Obtain Information About Suspended Statements
The following views can be queried to obtain information about the status of resumable statements:
Using the DBMS_RESUMABLE Package
The DBMS_RESUMABLE package helps control resumable space allocation. The following procedures can be invoked:
When a resumable session is suspended, an operation-suspended alert is issued on the object that needs allocation of resource for the operation to complete. Once the resource is allocated and the operation completes, the operation-suspended alert is cleared. Please refer to "Managing Space in Tablespaces" for more information on system-generated alerts.
Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger
In the following example, a system wide AFTER SUSPEND trigger is created and registered as user SYS at the database level. Whenever a resumable statement is suspended in any session, this trigger can have either of two effects:
Here are the statements for this example:
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 10g Tutorial
Overview Of Administering An Oracle Database
Creating An Oracle Database
Starting Up And Shutting Down
Managing Oracle Database Processes
Managing Control Files
Managing The Redo Log
Managing Archived Redo Logs
Managing Datafiles And Tempfiles
Managing The Undo Tablespace
Using Oracle-managed Files
Using Automatic Storage Management
Managing Space For Schema Objects
Managing Partitioned Tables And Indexes
Managing Hash Clusters
Managing Views, Sequences, And Synonyms
General Management Of Schema Objects
Detecting And Repairing Data Block Corruption
Managing Users And Securing The Database
Managing Automatic System Tasks Using The Maintenance Window
Using The Database Resource Manager
Moving From Dbms_job To Dbms_scheduler
Overview Of Scheduler Concepts
Using The Scheduler
Administering The Scheduler
Distributed Database Concepts
Managing A Distributed Database
Developing Applications For A Distributed Database System
Distributed Transactions Concepts
Managing Distributed Transactions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.