Resumable_timeout

RESUMABLE_TIMEOUT is new parameter in 10g which allows DBAs to enable/disable resumable statements and specify resumable timeout at the system level. In 9i this parameter could only be set on session level.
Sometimes DBAs does not know how much space is required for UNDO,TEMP or datafile to complete a big job.By setting this parameter, any space issue does not break the job.It reports an error in Alert log and the job automatically resumes once DBA has fixed the issue.

Here is a test scenario from Metalink :

1. Make the datafile autoextend off

2. Create a big table on the tablespace where the datafile is autoextend off

3. Insert rows into the table until it exceeds the space required by the INSERT operation.

4. Monitor the alert.log:
you find the following info:
statement in resumable session 'User SYS(0), Session 17, Instance 1' was suspended due to ORA-01653: unable to extend table SYS.T1 by 100 in tablespace TEST1

5. Check the table WRI$_ALERT_OUTSTANDING or view DBA_OUTSTANDING_ALERTS : SQL> select decode(message_level, 5, 'WARNING', 1, 'CRITICAL') alert_level, reason from dba_outstanding_alerts where reason like '%resumable%';

6. Modify the datafile autoextend to ON and check the alert.log.


from... nice articles below :

http://oradbatips.blogspot.com/2007/02/tip-24-resumabletimeout-in-10g.html

http://www.dbapool.com/articles/122705.html