resumable_timeoutOracle Resumable Space AllocationSetting resumable_timeoutAlter session enable resumableUsing the dbms_resumable packageInside resumable space allocation

Oracle Resumable Space Allocation

Oracle

Tips by Burleson Consulting

[@more@]

Resumable space allocation

, introduced in

Oracle

9i, is for all tablespaces at the session level. Database operations are suspended when an out-of-

space

condition is encountered. These suspended operations automatically resume when the error condition disappears. In

Oracle

Database 10g, this can be enabled at the instance level. Besides this improvement, automatic alert notification is sent when an operation is suspended.

This feature can be enabled by the SQL command.

ALTER SYSTEM SET RESUMABLE_TIMEOUT = ;

(Substitute 3600 for 1 hour)

Setting resumable_timeout

Setting the resumable_timeout initialization parameter, you can enable

resumable space allocation

system 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.

Alter session enable resumable

The ALTER SESSION ENABLE

RESUMABLE

statement is used to activate

resumable space allocation

for a given session. Developers are able to embed the ALTER SESSION statement in programs to activate

resumable space allocation

. A new parameter, called

RESUMABLE

, is used to enable

resumable space allocation

for export, import and load utilities.

Statements do not suspend for an unlimited amount of time. A timed interval can be specified in the ALTER SESSION statement to designate the amount of time that passes before the statement wakes up and returns a hard return code to the user and rolls back the unit of work. If no time interval is specified, the default time interval of two hours is used.

When a

resumable

statement suspends because of an out of

space

condition, the following actions occur:

• A triggerable system event is initiated. Developers are able to code triggers that fire when a statement suspends.
• Entries are placed into system data dictionary tables. The data dictionary views dba_resumable and user_resumable can be accessed to retrieve the paused statement’s identifier, text, status and error message.
• Messages are written to the alert log identifying the statement and the error that caused the statement to suspend.

Using the dbms_resumable package

Oracle resumable space allocation

is governed by the dbms_resumable package.

To use

resumable space

management must you enable it on a session-by-session basis using the alter session enable

resumable

command. By default if the

space

condition is not corrected after 2 hours, then the transaction will fail. You can configure a larger or smaller value if your needs require. Once enabled,

Oracle

will automatically detect the

space

condition and suspend the session.

Oracle

will write an entry to the alert log that the session has been suspended. Additionally the DBA_RESUMABLE view maintains a record of all currently suspended sessions. Once the DBA has corrected the

space

problem the suspended session will automatically resume its operation at the point of suspension.

Oracle

also provides an after suspend system trigger event, that allows you to automate your response to a session suspend condition. Further the dbms_resumable package is provided to allow for management of

resumable space

management from within SQL or PL/SQL.

Inside resumable space allocation

Noted author Kirti Deshpande has some great notes on

Oracle resumable space allocation

, and this is an excerpt from one of his articles which discusses the internals of

Oracle resumable space

management:

Transactions and jobs fail for a number of reasons, e.g., application errors, data errors, hardware errors etc. In this paper, we will focus on failures due to disk space

errors.

The following three categories broadly address such disk

space errors that cause the jobs to fail in an Oracle database environment:

How to Use Resumable Space Allocation Enable/Disable Resumable Space Allocation in Oracle9i Database and Oracle

Database10g

Grant the use ALTER SESSION and

RESUMABLE

privilege to use this feature. The user may also need EXECUTE privilege on a new package called DBMS_RESUMABLE to carry out certain steps (described later in the paper)

After establishing the session, the user issues the following command:

ALTER SESSION ENABLE

RESUMABLE [TIMEOUT n] [NAME ’string’]; Enable/Disable Resumable Space Allocation in Oracle Database 10g In Oracle Database 10g, you have a choice of either using the same oracle9i Database procedure to enable/disable resumable mode for sessions or letting all sessions run in resumable mode by setting a new initialization parameter. Oracle

Database 10g introduces RESUMABLE_TIMEOUT parameter.

It defaults to a value 0, meaning no sessions will be

resumable mode by default. However, you can change the value of this dynamic parameter to allow future sessions to run in resumable mode using the timeout value set for this global parameter. There is no need to grant ALTER SESSION and RESUMABLE privilege to the users. You can either set RESUMABLE_TIMEOUT=7200(for 2 hours of timeout)in the init.ora file and bounce the database, or simply issue one of the following commands to enable resumable

operations for all new sessions.

ALTER SYSTEM SET RESUMABLE_TIMEOUT=7200:

ALTER SYATEM SET RESUMABLE_TIMEOUT=0 SCOPE=BOTH; (if using SPFILE)

Mon Feb 19 12:36;46 2007

statement in reumable session ‘user WEBUSER(106),Session 21,Instance1’was suspended due to ORA-01536:

space

quota exceeded for tablespace ’USERS”

v$Session-Wait view

As mentioned earlier the suspended session is actually waiting on an event.You can track all such sessions from V$SESSION-WAIT view using following query:

select sid, wait_time,

seconds-in-wait, state, event

from v$session-wait

where event like ’statement suspended%’;

Resumable Space Allocation in Oracle Utilities Starting with Oracle9i Database, the utilities SQL*Loader, EXPORT and IMPORT have additional parameters to make use of resumable space allocation

feature.

These utilities have three new parameters as described below:

1. RESUMABLE : For SQL* Loader if defaults to ’FALSE’ and for Export and Import it defaults to ‘ N’, meaning there is no resumable operation by default. Setting this parameter to ‘Y’(Export/Import) or ‘TRUE’ (for SQL*Loader) will trigger the utility to run in resumable space allocation mode. For SQL*Loader and Import the job will be suspended when it encounters any space error. For Export utility, the resumable operation applies to job running out of database disk space and not the file system uses the QUERY option with ORDER BY clause, then running the export with RESUMABLE =Y will suspend the job in case the sort of operation runs out of space in the temporary tablespace.

1. RESUAMBLE_TIMEOUT: Determines how long the job can stay suspended before correcting the space error. Expressed in seconds.

1. RESUMABLE_NAME: Assigns a user-defined name to the suspended job.

Limitations of Resumable Space Allocation If you use Dictionary Managed tablespaces in your databases then there are a couple of limitations of resumable space allocation feature. Certain operations will not be resumable

.

1. If you are creating a table or index specifying the storage clause with maxextents not to set to unlimitied, then this DDL operation will fail fit encounters

space error even when run as a resuamble statement. The workaround is to specify unlimited for maxextents, or create the object in Locally Managed tablespace. If desired, you can change the value of maxextents after creating the object in the Dictionary Managed tablespace. This limitation only affects CREATE statements. Subsequent DML operations can be performed in resumable

mode.

2 - The other limitation applies to rollback segments. If those are in a Dictionary Managed tablespace, then

resumable space allocation does not work for statements encountering any space error related to rollback segments. To be able to use resumable space allocation feature in such cases, you must create the rollback segments in a Locally Managed tablespace, or use Automatic Undo Management feature, which by default uses Locally Managed tablespace for undo segments.

1. Unable to Extend Segment

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/450962/viewspace-1027518/，如需轉載，請注明出處，否則将追究法律責任。

轉載于:http://blog.itpub.net/450962/viewspace-1027518/