Enable the Resumable space allocation in Oracle

Leave a reply

Avoid space error due to tablespace while running operation in Oracle

During long running operation and import process failed due to the space issue or space running out in tablespace. So our current running process is suspended or stopped and we have to restart again.

  1. Unable to Extend Segment
  2. Maximum Extents Reached
  3. Space Quota Exceeded

Avoid these errors: ORA-01536, ORA-1629, ORA-1632, ORA-1650, ORA-1651, ORA-1652, ORA-1653, ORA-1654, ORA-1655

By using the Resumable space allocation option to resume our running operation from current statement. By default this setting is disabled:

Check the parameter value at instance level:

SQL> show parameter resumable
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout		     integer	 0
Note: 0 Means disable

Enable or disable at Session level:

-- For Enable 2 hours by default,
ALTER SESSION ENABLE RESUMABLE;
OR
-- For Enable specify the timing 
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
OR

-- For Enable we can use this package also
EXECUTE Dbms_Resumable.Set_Timeout(3600);

--For disable

ALTER SESSION DISABLE RESUMABLE;

Enable or disable at instance Level:

-- Enable for 1 hour = 3600 Seconds
ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600 scope=both;

--Disable at instance level:

ALTER SYSTEM SET RESUMABLE_TIMEOUT=0 scope=both;

Example: To describe the error we have to take two session one for having long running transaction which full the tablespace and cause space crunch and another to check the resumable transaction is waiting for resume.

For checking the transaction and error, we can check alert log file and we have one view to check 

set pagesize 100
column sql_text format a60
column error_msg format a60
SELECT status, name, sql_text, error_msg from dba_resumable;

Note: If you find any error then you have to add datafile in the tablespace space or enable autoextend on for datafile. Max size of data file is 32 GB in Oracle. For this use the following link as below:

unable to extend table by 8192 in tablespace system

Leave a Reply