Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Oracle9i - Resumable Space Allocation

The ALTER SESSION ENABLE RESUMABLE statement is used to activate resumable space allocation for a given session. Developers are able to embed the ALTER SESSION statement in programs to activate resumable space allocation. A new parameter, called RESUMABLE, is used to enable resumable space allocation for export, import and load utilities.

Statements do not suspend for an unlimited amount of time. A timed interval can be specified in the ALTER SESSION statement to designate the amount of time that passes before the statement wakes up and returns a hard return code to the user and rolls back the unit of work. If no time interval is specified, the default time interval of two hours is used.

When a resumable statement suspends because of an out of space condition, the following actions occur:

• A triggerable system event is initiated. Developers are able to code triggers that fire when a statement suspends.
   

• Entries are placed into system data dictionary tables. The data dictionary views dba_resumable and user_resumable can be accessed to retrieve the paused statement’s identifier, text, status and error message.
   

• Messages are written to the alert log identifying the statement and the error that caused the statement to suspend.

The Oracle Database Fundamentals I (1Z0-031) certification test will have a few questions the new Resumable Space Allocation feature provided in Oracle9i.  Test candidates should understand:
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.