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;
/