Oracle Database Tips by Donald Burleson
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
This feature can be enabled by the SQL command.
ALTER SYSTEM SET
RESUMABLE_TIMEOUT = <value in seconds>;
(Substitute 3600 for 1 hour)
Setting the resumable_timeout initialization
parameter, you can enable resumable space allocation system and specify a
timeout interval by setting the resumable_timeout initialization
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
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
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:
Unable to Extend Segment
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
the job cannot create or allocate a new extent for the segment in question,
the job aborts and Oracle reports an error stating so. The Oracle error
message will state the name of the segment and its tablespace name. Oracle
errors ORA-1650 to ORA-1655 fall in this category.
2. Maximum Extents
When the number of extents
for the segment in question reaches the maximum value set by its maxextents
parameter, the job aborts. The Oracle message will state the maximum number
of extents along with the segment name. Oracle errors ORA-1629 to ORA-1632
fall in this category.
3. Space Quota Exceeded
When the user exceeds the
assigned space quota for the segment's tablespace then the job aborts and
Oracle reports following error:
ORA-01536-'space quota exceeded for tablespace ?o/oS???
In the past several versions,
oracle Corporation introduced a number of new techniques and removed some
restrictions to allow objects to grow. You can minimize ?unable to extend? error
using auto-extensible data files or you can set maxetents to ?unlimited? to
avoid ?max# extents reached? error. However, not everyone may implemental these
features in all databases one supports for one reason or another. Reviewing and
adjusting storage clause for all segments and datafiles to minimize or avoid out
of disk space errors is a daunting task. Therefore, there will be incidents of
jobs failing due to disk space errors. This is when the Resumable Space
Allocation feature can be of immense help.
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
After establishing the session, the user issues the
ALTER SESSION ENABLE RESUMABLE [TIMEOUT n] [NAME
Enable/Disable Resumable Space Allocation in Oracle
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
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
Mon Feb 19 12:36;46 2015
statement in reumable session ?user
WEBUSER(106),Session 21,Instance1?was suspended due to ORA-01536: space
quota exceeded for tablespace ?USERS?
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:
seconds-in-wait, state, event
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
- 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
- RESUAMBLE_TIMEOUT: Determines how
long the job can stay suspended before correcting the space error.
Expressed in seconds.
- 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
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.
In addition to the above
limitations, in Oracle9i resumable space allocation is not possible if
the statement accesses a remote database via a database link. Oracle will report
an error if the SQL statement in resumable session contains a reference to a
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.