With this resumable space allocation feature, whenever job fails, it will be suspended until DBA fix the issues and job will be resumed instead of restarting from scratch.
In oracle9i, resumable space allocation feature must be turned on in the session level using the ALTER SESSION ENABLE RESUMABLE statement. In oracle10g, a new parameter RESUMABLE_TIMEOUT was introduced, where the resumable space allocation feature can be turned on at the database level. The default for this parameter is 0, which means the resumable timeout is not enabled.
Steps to setup Resumable space allocation:
Let us assume, we are enabling resumable job allocation for scott user.
Step1.
We need to grant RESUMABLE system privileges to schema.
GRANT RESUMABLE TO SCOTT;
Step2.
We need to setup the resumable timeout in seconds. The operation will be suspended for specified number of seconds for DBA to fix the problem.
There are several ways we can set timeout parameter.
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'emp data loading';
We can set RESUMABLE_TIMEOUT parameter in init parameter file. This new parameter was introduced in oracle10g. Resumable space allocation feature will be disabled automatically when RESUMABLE_TIMEOUT parameter is zero. We can set this parameter in session level as well as database level.
alter session set resumable_timeout=3600;
alter system set resumable_timeout=3600;
What kind of issues can be suspended in resumable space allocation?
Out of space:
We have an out of space error when we can not get any more extents for various database objects in a tablespace.
Maximum extents reached:
Number of extents are exceeded the maximum extents which we specified for the object.
Space Quota Exceeded:
User exceeds the space quota for the tablespace. when we create the user, we set the space quota. Operation will be suspended when user exceeds the space quota on tablespace.
What kind of operations can fall into resumable space allocation?
1. Queries that run out of temporary space for sorting.
2. DML Insert, update statement
3. DDL Create table as select, alter table, create index, alter index,
create materialized view, create materialized view log etc
4. Import/export
5. SQL Loader
Let us test resumable space allocation on scott schema. The below PLSQL code runs without resumable space allocation. When we run this code, it is failed right away....
scott@orcl> alter session set resumable_timeout=0;
Session altered.
scott@orcl> begin
2 for i in 1 .. 5000
3 loop
4 insert into employee
5 select * from employee;
6 commit;
7 end loop;
8 end;
9 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.EMPLOYEE by 8 in tablespace USERSPACE
ORA-06512: at line 4
scott@orcl>
Let us test the same code with resumable space allocation. Now i set the timeout parmeter 60 seconds. The session suspends for 60 seconds and throw error. Since we did not fix the error during the 60 seconds.
scott@orcl> alter session set resumable_timeout=60;
Session altered.
scott@orcl> set time on
11:37:24 scott@orcl>
11:37:24 scott@orcl> begin
11:37:24 2 for i in 1 .. 5000
11:37:24 3 loop
11:37:24 4 insert into employee
11:37:24 5 select * from employee;
11:37:24 6 commit;
11:37:24 7 end loop;
11:37:24 8 end;
11:37:24 9 /
begin
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table SCOTT.EMPLOYEE by 8 in tablespace USERSPACE
ORA-06512: at line 4
11:38:26 scott@orcl>
Let us test the same PLSQL code with higher timeout seconds. Let me set 1000 seconds.
scott@orcl> ALTER SESSION ENABLE RESUMABLE TIMEOUT 1000 NAME 'emp data loading';
Session altered.
scott@orcl> begin
2 for i in 1 .. 10
3 loop
4 insert into employee
5 select * from employee;
6 commit;
7 end loop;
8 end;
9 /
The above PLSQL procedure is hanging... Let me connect another session and check the status.
scott@orcl> connect scott/tiger@orcl
Connected.
system@orcl> select name,sql_text,status,error_msg from dba_resumable;
NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
STATUS
---------
ERROR_MSG
--------------------------------------------------------------------------------
emp data loading
INSERT INTO EMPLOYEE SELECT * FROM EMPLOYEE
SUSPENDED
ORA-01653: unable to extend table SCOTT.EMPLOYEE by 8 in tablespace USERSPACE
scott@orcl>
It seems like, the session is suspended and waiting the space to load the data. Let me connect in another session and increase the space for the tablespace.
system@orcl> connect system/password@orcl
Connected.
system@orcl> alter database datafile
2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERSPACE.DBF' resize 1000M;
Database altered.
system@orcl>
Again i will check the status of this job.
scott@orcl> connect scott/tiger@orcl
Connected.
scott@orcl> select name,sql_text,status,error_msg from dba_resumable;
NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
STATUS
---------
ERROR_MSG
--------------------------------------------------------------------------------
User SCOTT(57), Session 132, Instance 1
INSERT INTO EMPLOYEE SELECT * FROM EMPLOYEE
NORMAL
scott@orcl>
As per the above query, the status is back to normal. It means, the job is resumed. The error message is disappeared.
Again i went back and checked the job. The job is also successfully completed after increasing the space.
scott@orcl> begin
2 for i in 1 .. 10
3 loop
4 insert into employee
5 select * from employee;
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
scott@orcl>
Usage of AFTER SUSPEND TRIGGER:
It is impossible to check the suspended operations manually every time. Also suspended operation will not produce any error message. We can write the AFTER SUSPEND trigger to capture the suspended operations as well as error message. In AFTER SUSPEND trigger, we can write a code by using DBMS_SMTP package to notify DBA about suspended operations. We can also write the error message in log table by using autonomous transactions.
Here is the sample AFTER SUSPEND trigger skeleton.
CREATE OR REPLACE TRIGGER suspended_operations
AFTER SUSPEND
ON DATABASE
DECLARE
-- Declare any variables
BEGIN
-- Alter default timeout period.
Dbms_Resumable.Set_Timeout(3600);
-- Capture the error message into log table
-- Send email/page to DBA to make corrective action
END;
/
How do we see which operations are suspended in the database?
a) We can check the alert log
b) We can view USER_RESUMABLE or DBA_RESUMABLE
Resumable space parameters in imp/exp, SQL Loader:
We can use these below parameters to suspend the (SQL Loader, imp/exp) data load if there is space failure.
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
DBMS_RESUMABLE Package: This package is created for managing resumable space. SYS user should grant execute privileges to other schema to use this package.
ABORT(sessionID) - Ends the specified suspended session. Caller must be the owner of the session with sessionID, have ALTER SYSTEM privilege, or have DBA privileges.
GET_SESSION_TIMEOUT(sessionID) - Returns the timeout period in seconds for the specified session, or -1 if the session does not exist.
SET_SESSION_TIMEOUT(sessionID, timeout) - Sets the timeout in seconds of the specified session with immediate effect.
GET_TIMEOUT() - Returns the timeout period in seconds for the current session.
SET_TIMEOUT(timeout) - Sets the timeout in seconds of the current session with immediate effect.
DBMS_RESUMABLE.SPACE_ERROR_INFO(...) - Returns information about the space error when called from within an AFTER SUSPEND trigger.
Note : We use the below command to enable or disable resumable space allocation in oracle9i.
alter session enable resumable
alter session disable resumable
But in oracle10g, we do not need to use these command to enable or disable resumable allocation. Oracle10g introduced new parameter RESUMABLE_TIMEOUT, we use this parameter to enable or disable resumable space allocation.
alter session set resumable_timeout = 0 (Disable resumable space allocation)
alter session set resumable_timeout = 60 (enable resumable space allocation)
