Managing Resumable Space Allocation
------------------------------------
Resumable space allocation allows you to temporarily suspend operations that run out of space while you correct
the space issue without aborting the operation.

You can explicitly make operations run in the Resumable Space Allocation mode by using the
ALTER SESSION command. The Resumable Space Allocation feature will just suspend operations
until the problem is fixed (such as by you adding a data file to extend space) and it will resume automatically
after that.


Resumable Operations
---------------------
Specific Data Definition Language (DDL), Import/Export, Data Manipulation Language
(DML), and query statements are candidates for resumable executions:
NN SELECT statements that run out of sort area temporary space
NN INSERT, UPDATE, DELETE, and INSERT INTO…SELECT
NN Export/import and SQL*Loader
NN The following DDL statements:
CREATE TABLE AS SELECT
CREATE INDEX
ALTER TABLE MOVE PARTITION
ALTER TABLE SPLIT PARTITION
ALTER INDEX REBUILD
ALTER INDEX REBUILD PARTITION
ALTER INDEX SPLIT PARTITION
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG



SQL> GRANT RESUMABLE TO salapati;
You can enable a session for Resumable Space Allocation in one of two ways—set the RESUMABLE_TIMEOUT initialization parameter, or use the ALTER SESSION command to enable and disable resumable space allocation.

To enable resumable operations for the instance, alter the instance parameter RESUMABLE_TIMEOUT to a numeric value greater than zero. The default value is 0, which in effect initially disables resumable operations for all sessions. This represents the number of seconds that
an operation may suspend while you take corrective action. After the time-out is reached, the
operation will abort.

For example, to enable all database sessions for Resumable Space Allocation for a period of two hours, you’d set the parameter this way:
ALTER SYSTEM SET RESUMABLE_TIMEOUT=7200 SCOPE=SPFILE;

OR

Using the ALTER SESSION Statement
---------------------------------
You can enable Resumable Space Allocation in your session regardless of whether you’ve set the RESUMABLE_TIMEOUT initialization parameter:
SQL> ALTER SESSION ENABLE |DISABLE RESUMABLE;   

The default resumable time-out for a session is 7,200 seconds.
OR

You can also set the timeout interval using the DBMS_RESUMABLE package, as follows:
SQL> EXECUTE DBMS_RESUMABLE.set_session_timeout(4349,18000);

In the preceding example, the first number in the parentheses, 4349, is the SID of the session
for which you want the timeout to be enforced. You can omit the SID if you’re setting the timeout for
the current session. The second number, 18000, is the timeout period.

SQL> select dbms_resumable.get_timeout from dual;
GET_TIMEOUT
-----------
7200


SELECT * FROM DBA_RESUMABLE;


Optional
--------
Create a trigger every time the user logins to enable the feature
CREATE OR REPLACE TRIGGER at_logon_set_resumable_session
AFTER LOGON ON helpdesk.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session enable resumable timeout 1200';
END;
/


Every time the user helpdesk logins and some transactions fail due to the lack of space a record is created at dba_resumable view.

SELECT USER_ID, SESSION_ID, STATUS, START_TIME, SUSPEND_TIME, SQL_TEXT, ERROR_NUMBER, ERROR_MSG
FROM dba_resumable;




Disable
--------
ALTER SYSTEM SET RESUMABLE_TIMEOUT=0 SCOPE=SPFILE;

Similarly, you can disable the feature by using:
 ALTER SESSION DISABLE TIMEOUT statement.













References:
Sybex 11g OCP(lesson & lab lessons)