8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Resumable Space Allocation

Long running operations such as imports and batch processes sometimes fail because the server is unable to allocate more extents for an object. This may be because the object has reached max_extents or there isn't sufficient room in the tablespace for the object to expand. In previous releases the operation would have to be rerun, possible with some manual cleanup necessary. In Oracle9i operations that would fail due to space allocation problems can be suspended and restarted once the problem is fixed.

Resumable Mode

Operations can be made resumable by explicitly switching the session mode.

ALTER SESSION ENABLE RESUMABLE;
ALTER SESSION DISABLE RESUMABLE;

When the session is in resumable mode, any operations that result in the following errors will be suspended:

Once the error is corrected the operations will automatically resume.

Timeout Period

Operations will remain in a suspended state until the timeout period, 2 hours by default, is reached. The timeout period can be modified using any of the following commands.

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
EXECUTE Dbms_Resumable.Set_Timeout(3600);
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'insert into table';

The final example can be used to assign a name to the suspended session.

AFTER SUSPEND Trigger

Since suspended operations do not produce error messages, an alternative method is required to notify users/DBAs so that the problem can be corrected. Typically, these procedures are initiated using the AFTER SUSPEND trigger which always fires as an autonomous transaction. This trigger can be used to insert rows into an error table or email an operator using the DBMS_SMTP package.

CREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND
ON DATABASE
DECLARE
  -- Declare any variables
BEGIN
  -- Alter default timeout period.
  Dbms_Resumable.Set_Timeout(3600);

  -- Perform resumable space allocation
  -- notification code here.
  COMMIT;
END;
/

Views

Information about suspended sessions can be viewed via the USER_ and DBA_RESUMABLE views. When a session is suspended a row is added to the V$SESSION_WAIT view with the EVENT column containing "suspended on space error".

DBMS_RESUMABLE Package

Restrictions

For more information see:

Hope this helps. Regards Tim...

Back to the Top.