Guaranteeing Undo Availability


We discussed the option to guarantee undo retention in the section titled "The Significance of the UNDO_RETENTION Parameter" earlier in this chapter. You can enable the guarantee option by specifying the RETENTION GUARANTEE clause for the undo tablespace when it is created or at a later period using the ALTER TABLESPACE statement. The UNDO_RETENTION parameter is valid only if the current undo tablespace has sufficient space.

If you have more than one undo tablespace, you can switch from one tablespace to another for running longer batch jobs or for maintenance purposes. This is analogous to using larger rollback segments in Oracle 8i for long-running queries. Because the UNDO_TABLESPACE initialization parameter is dynamic, you can use the ALTER SYSTEM SET UNDO_TABLESPACE statement to switch to a new undo tablespace.

Errors During Undo Tablespace Switching

If the new tablespace (UNDOTBS2, as in the preceding code) does not exist, if the tablespace is not an undo tablespace, or if it is already being used by another instance (RAC environment only), an error will be generated and switching will not occur.


 ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2; 

The database has to be online and user transactions can be executed while the switch operation is executed. When the switch operation completes successfully, all new transactions started during and after the switch operation began are assigned to the new undo tablespace.

The switch operation does not wait for pending transactions in the old undo tablespace to commit. If there are any pending transactions, the old undo tablespace will enter PENDING OFFLINE mode. Existing transactions can continue there until committed and the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode.

If you set the parameter value for UNDO TABLESPACE to '' (two single quotes), then the current undo tablespace will be switched out and the next available undo tablespace will be switched in. If there are no free undo tablespaces available, this operation will fail.

 ALTER SYSTEM SET UNDO_TABLESPACE = ''; 



    Oracle Database 10g Insider Solutions
    SUSE LINUX Enterprise Server 9 Administrators Handbook
    ISBN: 672327910
    EAN: 2147483647
    Year: 2006
    Pages: 214

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net