Oracle9i New Feature Series: Resumable Space Allocation
Do you often have issues with batch jobs running out of
space producing unable to extent errors in the database? Now Oracle can suspend
the session in error until you add more space and resume the session from where
it left
.
Resumable space allocation solution can be used for the
following errors:
- · ORA-1650
unable to extend rollback segment ... in tablespace ...
- · ORA-1653
unable to extend table ... in tablespace ...
- · ORA-1654
unable to extend index ... in tablespace ...
- · ORA-1628
max # extents ... reached for rollback segment ...
- · ORA-1631
max # extents ... reached in table ...
- · ORA-1654
max # extents ... reached in index ...
· ORA-1536
space quote exceeded for tablespace string
The session need to enable the resumable mode using:
ALTER SESSION ENABLE RESUMABLE;
The following views can be queried to obtain information
about the status of resumable statements:
|
View
|
Description
|
|
DBA_RESUMABLE
USER_RESUMABLE
|
These views contain rows for all currently executing or
suspended resumable statements. They can be used by a DBA, AFTER SUSPEND
trigger, or another session to monitor the progress of, or obtain specific
information about, resumable statements.
|
|
V$SESSION_WAIT
|
When a statement is suspended the session invoking the
statement is put into a wait state. A row is inserted into this view for the
session with the EVENT column containing "statement
suspended, wait error to be cleared".
|
To read more:
OTN 9i Daily
Feature November 26
Oracle
Documentation Administrators Guide Chapter 14
Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA |
Questions/Comments? Write to webmaster@bijoos.com.
© The scripts, tips and articles appearing on BijooS.com cannot be reproduced
elsewhere without the prior permission from the webmaster.
|
|
