Oracle Resumable Space

If you were running a long batch program and the tablespace run out of space, this would cause a error, you would increase the amount of space in the tablespace and rerun your job, this could take quite a bit of time.

Oracle's resumable space will suspend the running job that has run into problems due to lack of space and will automatically continue when the space issue has been fixed.

You can make all operations run in resumable space allocation mode by using a alter session command. The following database operations are resumable

Queries - they can always be resumed after the temporary tablespace has run out of space.
DML Operations - insert, delete and update can all be resumed
DDL Operations - index operations involving creating, rebuilding and altering are resumable as are create table as select operations
import and export operations - SQL loader jobs are resumable but to must use the resumable parameter in the SQL loader job.

You can resume operations that are of the following types of errors

out of space - typical error message is the ORA-01653
maximum extents errors - typical error message is the ORA-01628
users space quota errors - typical error message is the ORA-01536

Privilege	grant resumable to vallep; grant execute on dbms_resumable to vallep;
Who has privilege	select grantee, privilege from dba_sys_privs where privilege='RESUMABLE';
Who has set resumable mode	select user_id, session_id, status, timeout, name from dba_resumable;
Who is in resumable mode waiting for space	select user_id, session_id, name, timeout, start_time, suspend_time from dba_resumable; select username, event from v$session where event like '%sus%';
Resumable space across entire database	resumable_timeout=7200; Note: default is 0, time is in seconds
Resumable space in session	alter session enable resumable;
Resumable space in session with timeout	alter session enable resumable timeout 18000; execute dbms_resumable.set_timeout(18000); Note: time is in seconds
Resumable space in session and adding a name	alter session enable resumable name 'pauls_resumable';
Display resumable space mode options	select dbms_resumable.get_timeout() from dual;
Disable resumable space mode	alter session disable resumable;
Useful Views
DBA_SYS_PRIVS	describes system privileges granted to users and roles.
DBA_RESUMABLE	lists all resumable statements executed in the system.
V$SESSION	lists session information for each current session