7. Shut down the source database, and open it normally.
Resumable Space Allocation
Many operations can fail for reasons of inadequate space. This typically shows up as
an inability to add another extent to a segment, which itself can have several causes:
a datafile could be full; an auto-extensible datafile or tempfile could be on a disk that
is full; an undo segment could be in an undo tablespace that is full; an operation
requiring temporary space could be using a temporary tablespace that is full; or a user
could have reached their quota limit on a tablespace. Whatever the reason, space-related
errors tend to be dreadfully time consuming.
Consider an exercise to load data into a data warehouse. The first time you attempt
this, it fails because the destination tablespace runs out of space. The data that did go
in must be rolled back (which may take as long as the insert), the tablespace extended,
and the load done again. Then it fails because of inadequate undo space; roll back,
increase the undo tablespace, and try again. Then it fails during index rebuilding,
because of a lack on temporary space. And so on. Exercises such as this are the bane
of many DBAs’ lives. The resumable space allocation feature can be the solution.
If you enable resumable space allocation, when an operation hits a space problem
(any space problem at all) rather than failing with an error (and in many cases rolling
back what it did manage to do) the operation will be suspended. To the user, this will
show as the session hanging. When the error condition is resolved, it will continue.
All suspended sessions (currently suspended and previously suspended but now
running again) are listed in the view DBA_RESUMABLE.
To enable resumable space allocation at the session level, the command is
alter session enable resumable [ timeout <seconds> ] [ name <opname> ] ;
The TIMEOUT option lets you specify for how long the statement should hang. If
this time is reached without the problem being resolved, the error is returned and the
statement fails. If there is no specified TIMEOUT, the session will hang indefinitely.
The NAME option lets you specify a name that will be displayed in the DBA_RESUMABLE
view, which can help you determine at which point in a multistatement process the
space problem occurred.
TIP It is possible for a process to be suspended and resumed many times.
The DBA_RESUMABLE view will show you details of the current or the last
suspension.
It is also possible to enable resumable space for all sessions, by setting an instance
parameter. This is a dynamic parameter. For example, to set a timeout of one minute:
alter system set resumable_timeout=60;
This will cause all sessions that hit a space problem to be suspended for up to one
minute.
TIP The expdb and impdp Data Pump utilities have a command-line switch
RESUMABLE=Y (the default is N) that will allow Data Pump jobs to suspend if
they hit space problems.
EXAM TIP While a session is suspended, it will retain control of all the
resources it is using, including: undo space, temporary space, PGA memory,
and record locks.
There is little point in enabling resumable space allocation for a session or the
instance if you don’t do anything about the problem that caused a session to be
suspended. Suspended sessions will, by default, be reported through the server alert
system (fully described in Chapter 24), be displayed by Database Control, and be
P AR T III
listed in the DBA_RESUMABLE data dictionary view. Having spotted a problem, you
can fix it interactively from another session. Or you can create a trigger: an AFTER
SUSPEND ON DATABASE trigger that will run whenever a session is suspended. This
trigger could report the problem (perhaps by generating an e-mail), or it could include
code to investigate the problem, and fix it automatically. For example, to send an e-mail:
create trigger detect_suspendafter suspend on databasebeginutl_mail.send(sender=>'
[email protected]',recipients=>'
[email protected]',subject=>'DB session suspended',message=>'resumable space allocation event occurred');end;
TIP If you create an AFTER SUSPEND ON DATABASE trigger that attempts
to fix problems, remember that it might hit the same problem.
