Oracle DBA Tips and Techniques

By Arjun Raja

Resumable space allocation.

Posted by Gavin Soorma on June 29, 2009

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. 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 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: