posted Feb 4, 2012, 12:00 AM by Sachchida Ojha   [ updated Feb 4, 2012, 12:01 AM ]
Oracle database provides a way to suspend long-running operations in the event of space allocation failure. Once the DBA is notified and space issue has been corrected, the long running operation can resume and complete. No need to restart the long running operations.

There are 3 types of space management problem that can be addressed with RESUMABLE SPACE ALLOCATION.

1. Out of space in the tablespace - (Common issue during impdp from upper environment to lower environment)

2. Maximum extents reached in the segment

3. Spece Quota exceeded for a user

DBA can automatically make statement resumable by setting init.ora parameter RESUMABLE_TIMEOUT = 5000 (value >0). This value is in seconds.

It can also be done in the session level,

SQL> alter session enable resumable timeout 5000;

SQL> desc dba_resumable;
 Name                               Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 USER_ID                            NUMBER
 SESSION_ID                            NUMBER
 INSTANCE_ID                            NUMBER
 COORD_INSTANCE_ID                        NUMBER
 COORD_SESSION_ID                        NUMBER
 STATUS                             VARCHAR2(9)
 TIMEOUT                            NUMBER
 START_TIME                            VARCHAR2(20)
 SUSPEND_TIME                            VARCHAR2(20)
 RESUME_TIME                            VARCHAR2(20)
 NAME                                VARCHAR2(4000)
 SQL_TEXT                            VARCHAR2(1000)
 ERROR_NUMBER                            NUMBER
 ERROR_PARAMETER1                        VARCHAR2(80)
 ERROR_PARAMETER2                        VARCHAR2(80)
 ERROR_PARAMETER3                        VARCHAR2(80)
 ERROR_PARAMETER4                        VARCHAR2(80)
 ERROR_PARAMETER5                        VARCHAR2(80)
 ERROR_MSG                            VARCHAR2(4000)

SQL> select  USER_ID, INSTANCE_ID, STATUS, NAME, ERROR_MSG from dba_resumable;

no rows selected