Thursday, April 06, 2006

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

5 Comments:

At Thu Apr 27, 03:28:00 AM, Anonymous Sachin said...

After u run into the "statement in resumable session " message in alert log, u correct the condition by adding more quota and then show that running the insert goes thru. It was a bit unclear since i think the dba has to run a alter database resume command to resume all hung jobs.

Your blog is very informative.

 
At Thu Apr 27, 02:49:00 PM, Blogger lutz_hartmann said...

Hi Sachin,
no, the suspended statement will resume automatically if the cause for the suspension is fixed within the given timeout periode.
If this is not the case then the statement may issue the following error:
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'EXAMPLE'

Starting with Oracle 10g you can set a timeout with the new initialization parameter RESUMABLE_TIMEOUT on SYSTEM level as well. Before this was only possibel with ALTER SESSION... ( as in my post)
Hope this helps.
Regards,
Lutz

 
At Fri Apr 28, 05:29:00 AM, Anonymous Sachin said...

Thanks for the clarification.

 
At Fri Apr 28, 06:08:00 AM, Blogger lutz_hartmann said...

Welcome any time,
regards,
Lutz

 
At Wed Mar 19, 09:28:00 AM, Blogger jvj said...

hi Lutz

This concept is very clear and excellent one.

but i have a small doubt here....

i.e

if we enable this concept at system level whether it causes any performence issues? and

one more

are there any dependent parameters?


Thanks,
Jvj.

 

Post a Comment

<< Home