I just had a long data warehouse load process fail with out of space in rollback segment tablespace after 10 hours of run time, is there anything I can do to avoid this ? OR I just had a very critical business query fail after 9 hours of runtime, with temp space problem, is there anything I can do to avoid this?

You are just in luck, if you run Oracle 9i. Oracle introduced a new feature of resumable space management in this release. This feature is just what you are looking for. With the introduction of resumable space management, situations like your's should be a thing of past.

Tell me more !

Sure! With Resumable space allocation enabled a process that encounters space allocation failure, suspends execution instead of failing and rolling back the transaction. After the DBA takes corrective measure for the failure, the process automatically resumes execution from where it left of. A statement executing in the context of a session with resumable space allocation enabled is called a resumable statement. In the absence of resumable space allocation, space allocation failure would have caused the process to fail and rollback.

Hold on Einstien! What kind of space allocation failures are you talking about ?

The following table lists the errors I am referring to:

When any of the above errors are encountered by a resumable statement, then oracle does the following:

a) Suspends the resumable statement
b) Raises a error in the alert log.
c) Suspends the resumable session, until either the problem is resolved or the timeout period is passes.

After the statement is suspeneded, the session waits for a defined period of time (timeout: default 7200 seconds). After this timeout period passes the correspoding error is raised, the session fails and the transaction is rolled back. If during the suspension period the space allocation issues is fixed then the statement resumes automatically from where it left off.

During the suspension period the session holds on to all the transaction resources allocated (rollback segment space, transaction locks etc), but the temporary segment can be deallocated by other sessions. But the suspeneded query would resume without the user noticing any difference (except for the delay ofcourse).or without any user invervention, after the error condition is fixed.