Resumable Space Allocation

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:


The following views can be queried to obtain information about the status of resumable statements:





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.


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

Search Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to © The scripts, tips and articles appearing on cannot be reproduced elsewhere without the prior permission from the webmaster.