|
RESUMABLE SPACE ALLOCATION in Oracle 10g
This article describes a useful feature in 10G which allows the user to resume a
session from where it hung for reasons of space or other constraints.
RESUMABLE SPACE ALLOCATION AND AFTER SUSPEND TRIGGER
A resumable statement allows you to :
1. Suspend large operations instead of receiving an error
2. Allows you to fix the problem while the operation is suspended, rather than
start from scratch.
A session remains suspended for the following reasons
1. Tablespace runs out of space
2. Max extents reached.
3. Space quota exceeded on tablespace.
Pre-requisites:
1. INIT.ORA prameter : RESUMBABLE_TIMEOUT=3600( 1 HOUR) , default is 7200
seconds or 2 hours. This parameter can be changed
dynamically with an 'alter system set resumable_timeout=3600'
2. Grant RESUMABLE system privilege to schema owner/user.
3. User has to issue ' alter session enable resumable' before running the
transaction.
EXAMPLE:
Table test in arjun schema.
SQL> create table test (col_1 number(10),col_2 date);
Table created.
Create a script called loop.sql which is created in this manner..
vi loop.sql in unix .
declare
x number;
begin
x:= 0;
for i in 1 .. 5000
loop
insert into test
values
(x,sysdate);
x := x+1;
commit;
end loop;
end;
/
save the file as loop.sql
Now set the Resumable_timeout parameter to 0
SQL> alter system set resumable_timeout=0 scope=both;
System altered.
SQL> @loop
declare
*
ERROR at line 1:
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN
ORA-06512: at line 7
Now issue command as sysdba.
Sql> alter system set resumable_timeout=3600 scope=both;
Conn arjun/arjun
@loop
Session hangs after sometime but no error message is thrown( see below the use
of after suspend trigger to throw an error message when the session hangs)
From another session.
SQL> select count(*) from arjun.test;
COUNT(*)
----------
98900
SQL> /
COUNT(*)
----------
98900
SQL> /
COUNT(*)
----------
98900
Although space has run out tablespace arjun, the first session does not throw an
error but allows you to increase space in the tablespace from another session.
The first session will wait for 1 hour - the time set by you in the
resumable_timeout=3600 parameter. If you do not take corrective action, ie add
space within that period, an error is thrown and transaction rolled back.
SQL> select sum(bytes/1048576) from dba_free_space where tablespace_name='ARJUN';
SUM(BYTES/1048576)
------------------
.9375
Add space to tablespace..
SQL> Alter database datafile '/u03/ORACLE/test9/arjun.dbf' resize 5m;
Database altered.
As soon as you add space the table begins to get populated again..
SQL> select count(*) from arjun.test;
COUNT(*)
----------
119097
SQL> /
COUNT(*)
----------
136680
SQL> /
COUNT(*)
----------
145413
USAGE OF AFTER SUSPEND TRIGGER IN RELATION TO RESUMABLE SPACE ALLOCATION:
Another very useful feature is the usage of the AFTER SUSPEND trigger which
results in an error message appearing in the session, rather than the session
hanging( in which case the DBA or USER will never know there is an error unless
he checks from another session.)
This helps the DBA to take corrective action once the error message is thrown.
Once corrective action is taken , the original session can be restarted.
Firstly create the trigger as SYS user.
CREATE OR REPLACE TRIGGER ARJUN_RESUMABLE
AFTER SUSPEND ON DATABASE
BEGIN
DBMS_RESUMABLE.SET_TIMEOUT(60);
END;
/
This creates a trigger which sets the RESUMABLE_TIMEOUT to 60 seconds or 1
minute if there is a situation where a session is stuck. This is the time
allowed to take corrective action before oracle aborts the transaction.
Begin inserting into the TEST table..
@loop
Hangs after sometime...
To check if a session is hanging and the error message---
From another session just select error_msg from the dba_resumable view.
SQL> select error_msg from dba_resumable;
ERROR_MSG
--------------------------------------------------------------------------------
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN
SQL> select count(*) from arjun.test;
COUNT(*)
----------
353926
Now the original session that was running the insert hangs for 1 minute and then throws the error.
declare
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table ARJUN.TEST by 128 in tablespace ARJUN
ORA-06512: at line 7
Take corrective action(increase size of tablespace,etc) and then rerun the insert
@loop..
SQL> select count(*) from arjun.test; /* Notice the session has resumed and count increases. */
COUNT(*)
----------
416321
SQL> /
COUNT(*)
----------
421232
SQL> /
COUNT(*)
----------
425740
|
