Oracle dbms_resumable Robert Freeman tips by Donald Burleson

Using dbms_resumable

To use resumable space management must you enable it on a session-by-session basis using the alter session enable resumable command. By default if the space condition is not corrected after 2 hours, then the transaction will fail. You can configure a larger or smaller value if your needs require. Once enabled, Oracle will automatically detect the space condition and suspend the session. Oracle will write an entry to the alert log that the session has been suspended.

 Additionally the DBA_RESUMABLE view maintains a record of all currently suspended sessions. Once the DBA has corrected the space problem the suspended session will automatically resume its operation at the point of suspension.

Oracle also provides an after suspend system trigger event, that allows you to automate your response to a session suspend condition. Further the dbms_resumable package is provided to allow for management of resumable space management from within SQL or PL/SQL.