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 SQL> |