Oracle undo Reading Notes, oracleundo

Oracle undo Reading Notes, oracleundo

Two parameters related to undo:

Undo_tablespace

UNDO_MANAGEMENT = auto

After oracle 9i, we recommend that you use undo for automatic management;

Undo_retention

This parameter is used to ensure that the content in the undo rollback segment is not overwritten. However, this parameter must be considered together with the undo size setting (maxsize, unlimit.

Otherwise, it is meaningless.

When the undo tbs size is fixed, setting the undo_retention parameter is meaningless. Oracle does not guarantee that the undo retention content will not be rewritten within the interval set by undo retention.

The persistence of the content in undo directly affects oracle's consistent read, flash back, and long-running query.

Enable undo-retention:

Specify retention guarantee when creating a database or create undo tablespace;

Alter tablespace settings;

Use alter tablespace to disable undo-retention

Aler tablespace xxx RETENTION NOGUARANTEE

Oracle automatically adjusts undo retention, but does not support the lob field. undo information for LOBs is stored in the segment
Itself and not in the undo tablespace

How to determine the current undo retention policy:

Select to_char (begin_time, 'dd-MON-RR HH24: MI ') begin_time,
To_char (end_time, 'dd-MON-RR HH24: MI ') end_time, tuned_undoretention
From v $ undostat order by end_time;

How to Set undo_retention

Alter system set UNDO_RETENTION = 2400;

This parameter can be set dynamically and takes effect immediately, but it cannot be guaranteed that oracle will comply with our settings. All the prerequisites are subject to the undo size Setting Parameter and whether to set retention guarantee.