|
Large operations such as batch
updates or data loads can encounter out of space failures
after executing for a long period of time, sometimes when
they are just about to complete. Under such a circumstance,
database administrators are left with no other choice but
to re-execute the failed job under a close supervision in
order to ensure its successful completion. This
could be an extremely time and resource consuming exercise
which can disrupt business activities significantly especially
if the failed job is responsible for performing a critical
business task such as the month end financial closure.
Oracle9i introduces
a new feature called "Resumable Space Allocation"
which allows users to avoid such failures by instructing the
database to suspend any operations that encounter an out of
space error rather than aborting them. This provides an opportunity
for users or administrators to fix the problem that caused
the error while the operation is suspended and automatically
resume its execution once the problem has been resolved. This
feature enhances the ability of the Oracle database to tolerate
failures on account of unforeseen space requirements and eliminates
the wastage of precious administrator time and system resources
spent on re-execution of failed operations. By allowing administrators
to intervene in the middle of the execution of an operation,
Resumable Space Allocation obviates the need of dividing a
large job into smaller sub-jobs in order to limit the impact
of any failure. Additionally, it will also enable application
developers to write applications without worrying about running
into space related errors.
Resumable
Space Allocation allows administrators to prevent application
outages
A statement can be executed
in the "resumable" mode when explicitly specified
by using the ALTER SESSION ENABLE RESUMABLE command. Virtually
any kind of operation be it a PL/SQL stored procedure, Java
stored procedure, queries, SQL*loader, export/import, DML
(such as UPDATE, INSERT) and DDL (CREATE TABLE AS
SELECT…., CREATE INDEX , INDEX REBUILD, ALTER TABLE MOVE PARTITION
etc..) can all be run as a "resumable" statement.
A "resumable" operation will be suspended whenever
it encounters one of the following types of failures:
-
Out of space condition:
The operation can not acquire any more space in a tablespace
or when the tablespace can not extend itself by acquiring
additional disk space from the OS.
-
MAX Extents Reached:
The number of extents in a table, index, temp segment,
rollback segment, cluster, LOB, table partition, index
partition equals the maximum number of extents defined.
-
User space quota is
exceeded.
Once the operation is suspended,
a warning to that effect will be written in the alert log
file. A trigger can also be used on a new event called "AFTER
SUSPEND" either to generate a notification or take corrective
actions. Any transactions executed within the trigger will
automatically be executed as an autonomous transaction and
can therefore include operations such as inserts into a user
table for error logging purposes. Users may also access the
error data using the "DBMS_RESUMABLE" package and
the DBA(USER)_RESUMABLE view.
When the problem that caused
the failure is fixed, the suspended statement automatically
resumes execution. If the operation encounters a transient
problem which may disappear automatically after some time
such as temporary tablespace shortage, no administrator intervention
may be required to resume its execution. A "resumable"
query running out of temporary space may resume automatically
with absolutely no user or administrator intervention once
other active queries complete. A "resumable" operation
may be suspended and resumed multiple times during its execution.
Every "resumable"
operation has a time-out period associated with it. The default
value of time-out period is 2 hours but can be set to any
value using the ALTER SESSION ENABLE RESUMABLE TIMEOUT <time-out
period in seconds> command. A suspended operation will
automatically be aborted if the error condition is not fixed
within "time-out" period. An administrator can abort
a suspended operation any time using DBMS_RESUMABLE.ABORT()
procedure.
While running Parallel DML/Query,
if one of the server processes encounters a correctable error,
it will suspend its execution while other processes continue
executing their respective tasks, until either they too encounter
an error or are blocked by the suspended process. When the
correctable error is resolved, the suspended process resumes
execution but if it is aborted, the parallel operation aborts
as well.
More Info
Oracle9i
Database Daily Features
|
 |
Bookmark
