Resumable Space Allocation

Resumable Space Allocation

Resumable space allocation suspends a long-running operation in event of a space allocation error so that you can fix the problem. Then Oracle automatically resumes the long-running operation. Operations that are resumable are:

• Queries that run out of temporary sort space
• DML – INSERT, UPDATE, and DELETE statements
• DDL – CREATE TABLE AS SELECT, ALTER TABLE, CREATE INDEX, ALTER INDEX, and statements that create materialized views or materialized view logs
• Import/Export – SQL*Loader operations

The space problems resumable space allocation addresses are:

• Out of space
• Maximum extents reached
• Space quota exceeded

To use resumable space allocation, set your session as resumable, and ensure you have the RESUMABLE system privilege:

ALTER SESSION ENABLE RESUMABLE ;

GRANT RESUMABLE TO my_id ;

If an operation is suspended, Oracle writes an error to its Alert log. Views USER_RESUMABLE and DBA_RESUMABLE contain info on the error, or you can run DBMS_RESUMABLE.SPACE_ERROR_INFO for error details. Fix the error, then Oracle will automatically resume the suspended statement.

Oracle also provides the AFTER SUSPEND system event to handle resumable space errors. You could use this, for example, in a trigger with custom code:

CREATE OR REPLACE TRIGGER my_space_handler

AFTER SUSPEND ON DATABASE...