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

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


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


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