Resumable space allocation in ORACLE 10g

Since Oracle 9i we can cause a session which runs into a space allocation problem
to halt adn wait for the resolution of the space problem
instead of rolling back the staement
by using the resumable space allocation option.

In 9i this could be done only on session level.
Since Oracle 10gR1 the parameter RESUMABLE_TIMEOUT
can also be set on system level for all sessions;

Parameter type	Integer
Default value	0 (seconds)
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	0 to 231 - 1 (in seconds)
Real Application Clusters	Multiple instances can have different values.

Here is a little demo on session level:

SYS @ orcl AS SYSDBA SQL > CREATE USER lutz IDENTIFIED BY lutz
DEFAULT TABLESPACE lutz_klein
QUOTA 1m ON lutz_klein;
User created.

SYS @ orcl AS SYSDBA SQL > GRANT CREATE SESSION , CREATE TABLE TO lutz;
Grant succeeded.

LUTZ @ orcl SQL > BEGIN FOR i IN 1..10000 LOOP
2 INSERT INTO lutz_small VALUES(i);
3 END LOOP;
4 COMMIT;
5 END;
6 /
PL/SQL procedure successfully completed.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'LUTZ_KLEIN'

LUTZ @ orcl SQL > SELECT COUNT(*) FROM lutz_small;
COUNT(*)
----------
40000
As we can see, the server made a transaction level rollback.

LUTZ @ orcl SQL > ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
ERROR:
ORA-01031: insufficient privileges

SYS @ orcl AS SYSDBA SQL > GRANT RESUMABLE TO lutz;
Grant succeeded.
LUTZ @ orcl SQL > ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
Session altered.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;

alert_orcl.log ===>>
Thu Apr 6 10:12:48 2006pr 6 10:59:03 2006
statement in resumable session 'User LUTZ(62), Session 142, Instance 1' was suspended due to
ORA-01536: space quota exceeded for tablespace 'LUTZ_KLEIN'

SYS @ orcl AS SYSDBA SQL > DESC dba_resumable
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID NUMBER
SESSION_ID NUMBER
INSTANCE_ID NUMBER
COORD_INSTANCE_ID NUMBER
COORD_SESSION_ID NUMBER
STATUS VARCHAR2(9)
TIMEOUT NUMBER
START_TIME VARCHAR2(20)
SUSPEND_TIME VARCHAR2(20)
RESUME_TIME VARCHAR2(20)
NAME VARCHAR2(4000)
SQL_TEXT VARCHAR2(1000)
ERROR_NUMBER NUMBER
ERROR_PARAMETER1 VARCHAR2(80)
ERROR_PARAMETER2 VARCHAR2(80)
ERROR_PARAMETER3 VARCHAR2(80)
ERROR_PARAMETER4 VARCHAR2(80)
ERROR_PARAMETER5 VARCHAR2(80)
ERROR_MSG VARCHAR2(4000)


SYS @ orcl AS SYSDBA SQL > SELECT name, SQL_TEXT, STATUS, TIMEOUT FROM dba_resumable;
NAME SQL_TEXT STATUS TIMEOUT
---------- ------------------------------------------------- --------- ----------
User LUTZ( insert into lutz_small select * from lutz_small SUSPENDED 600
62), Session 142, Instance 1


SYS @ orcl AS SYSDBA SQL > ALTER USER lutz QUOTA UNLIMITED ON lutz_klein;
User altered.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;
40000 rows created.

alert_orcl.log ===>>
Thu Apr 6 11:05:29 2006
statement in resumable session 'User LUTZ(62), Session 142, Instance 1' was resumed