Job Seekers   Employers
dbapool: Authors | Submissions | Contact Us
   Forgot password? | Sign up
  Home   Discussion Forum   Articles   Interview Questions   FAQs   Scripts   Rewards   Analyzer   White Papers   Blog   Certification   Downloads   Tools
   
RESUMABLE SPACE ALLOCATION in Oracle 10g




By arjun raja
Dec 27, 2005

Digg! digg!     Print    email to friend Email to Friend

Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.




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


 

 

 
About author:

10g OCP with many topics on 10g to share.

 




  About Us Advertise Terms of Use Privacy Newsletters Contact Us    

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads 

Our Premium Sponsor