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.
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.
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.
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; /
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".
ABORT(sessionID)
- Ends the specified suspended session. Caller must be the owner of the session with sessionID,
have ALTER SYSTEM privilege, or have DBA privileges.
GET_SESSION_TIMEOUT(sessionID)
- Returns the timeout period in seconds for the specified session, or -1 if the
session does not exist.
SET_SESSION_TIMEOUT(sessionID, timeout)
- Sets the timeout in seconds of the specified session with immediate effect.
GET_TIMEOUT()
- Returns the timeout period in seconds for the current session.
SET_TIMEOUT(timeout)
- Sets the timeout in seconds of the current session with immediate effect.
DBMS_RESUMABLE.SPACE_ERROR_INFO(...)
- Returns information about the space error when called from within an AFTER SUSPEND trigger.
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/resumable-space-allocation