Resumable timeout parameter avoid space issue error in Oracle

DBMS_RESUMABLE help to configure alert for suspended session.

RESUMABLE_TIMEOUT:
resumable_timeout means that during execution of statements, statements will be suspended if there is no space available in the tablespace for that operation.
For long operation we can enable it so that we never lost our processed work in-between due to space issue in tablespace.
Example, our ETL job is running from 2 hours, at last we faced tablespace space issue then we need to processed it again.

show parameter resumable_timeout
NAME              TYPE    VALUE
----------------- ------- -------
resumable_timeout integer 0

Enable the resumable at System level

alter system set resumable_timeout = <value in seconds>;

-- For 1 hour time
ALTER system set resumable_timeout=3600;

Disable the resumable at system level

alter system set resumable_timeout=0;

Enable at session level with alter session enable resumable statement.

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

Check the status of resumable job present in database

col sql_text for a30
col error_msg for a30
select user_id, session_id, status, start_time, suspend_time, sql_text, error_number, error_msg
from dba_resumable;

Note: Column Details:
Timeout: Timeout of the resumable statement after specific time.
Start_time: of the resumable statement.
Suspended_time: Last time the resumable statement was suspended.
Resume_time: Last time the suspended resumable statement was resumed
ERROR_NUMBER: Error code of the last correctable error. When STATUS is set to RUNNING, its value will be 0.
ERROR_MSG: Error message corresponding to ERROR_NUMBER. It will be NULL when ERROR_NUMBER is 0.
STATUS: it will tell about current status of resumable job: RUNNING, SUSPENDED, TIMEOUT, ERROR, ABORTED

Create logon trigger for user for session level:

create or replace trigger t_resumable_user
 after logon
 on user_name
 begin
 execute immediate 'alter session enable resumable timeout 3600';
 end;
/

Configure mail alert for suspended session
Configure the alert by using the package dbms_resumable, whenever suspend event occur at database level.

Create or replace trigger alert_on_Suspended
after suspend
on database
declare
   v_ret_val boolean;
   v_err_type varchar2(30);
   v_obj_type varchar2(30);
   v_owner varchar2(30);
   v_tbs_name varchar2(30);
   v_obj_name varchar2(30);
   v_sub_obj_name varchar2(30);

begin
   --Get all error variables
   v_ret_val := dbms_resumable.space_error_info(
                  error_type => v_err_type,
                  object_type => v_obj_type,
                  object_owner => v_owner,
                  table_space_name => v_tbs_name,
                  object_name => v_obj_name,
                  sub_object_name => v_sub_obj_name);

--Set timeout to 1 hours.This is the time that DBAs have to fix space problem
   dbms_resumable.set_timeout(3600);

--use UTL_MAIL package for send alert
  execute immediate 'alter session set smtp_out_server = ''192.127.1.1''';
  utl_mail.send(sender => 'dba@gmail.com',
            recipients => 'dba@gmail.com',
               subject => 'suspended session alert',
               message => 'Check space problems!'||chr(10)||
                           'Error type:' ||v_err_type||chr(10)||
                           'Obj Name:' ||v_obj_name||chr(10)||
                           'Obj Type:' ||v_obj_type||chr(10)||
                           'Obj Owner:' ||v_owner||chr(10)||
                           'Tablespace Name:' ||v_tbs_name||chr(10)||
                           'Sub-object name:' ||v_sub_obj_name||chr(10),
             mime_type => 'text; charset=us-ascii');

end;
/

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.