Resumable Session A resumable session hangs instead of aborts when a space problem occurs. If the space problem is resolved within the timeout interval, the hanging session will resume its work. This is particularly useful if you have a big job and it is difficult to predict how much space it requires and you don't want to overallocate space, especially to the undo tablespace because it's not easy to free up afterward. The following are some notes about resumable sessions. * Parameter resumable_timeout Setting this parameter alone with "alter system" command without explicit "alter session enable resumable" will enable resumable for all sessions, i.e. change all sessions including future ones to be resumable. But dba_resumable or v$resumable will not show these implicitly enabled resumable sessions, until a session actually runs out of space and throws error and hangs. Another way to describe this is that setting this parameter alone causes the system to watch all sessions for any space problem and hangs them when the problem occurs, even though nothing is shown in the data dictionary views until the space problem occurs. Explicit "alter session enable resumable [timeout ]" takes precedence over implicit parameter setting and the session immediately shows up in dba_resumable and v$resumable. * Data pump quirk Data pump import automatically starts the session with resumable enabled. It alerts on the screen when the import of *table data* runs out of space. But it keeps silent when the import creates *indexes* and runs out of space. You must check dba_resumable (or indirectly dba_free_space) to find that out. Probably the only way to change resumable timeout for a data pump session is: after the data pump job has started and its session is shown in dba_resumable, alter system set resumable_timeout=. Without running this "alter system" command, the resumable timeout of the data pump session is 7200, regardless what value you have set it to earlier. This behavior is the same as a sqlplus session in which "alter session enable resumable" (without setting timeout) is executed. * Latch resumable state object Once a session is explicitly resumable, either by "alter session enable resumable" or having run out of space after "alter system set resumable_timeout=" was run earlier in the database, every execution of any SQL causes 3 gets on this latch. (In a database with a non-zero resumable_timeout, any session logon incurs 1 latch get.) From the point of view of reducing unnecessary latch gets, it may be better to set the parameter at system level alone than not setting it but enabling resumable at session level. * Event The only related event is 30030, 00000, "suppress resumable related error message" // *Cause: none // *Action: This event is to be used if the errors 30031 and 30032 // wish to be suppressed. * Data dictionary Dba_resumable takes longer to query because it joins GV$RESUMABLE, GV$SESSION, GV$TRANSACTION, GV$SQL, GV$PX_SESSION V$resumable is faster. Gv$_resumable2 has two additional columns, objid (tablespace number, i.e. tablespace$.ts#) and type. X$ktrso is the base table of all. The only unexposed columns are kssobflg and kssobown (which is v$session.saddr). Ref: http://www.freelists.org/post/oracle-l/Parameter-resumable-timeout,3