Oracle Undo

No Comments

When data is changed in the oracle database, the information needed to undo the changes is recorded so that the change can be undone (Rolled back). The record is basically just the original information.

Information is recorded in rollback segments and the undo tablespaces.

This information is also used to provide read consistency which allows long running transactions to always obtain the data as it was at the time that it began.
The recommended method of managing Undo is system managed Undo (SMU)
Set the UNDO_MANAGEMENT initialisation parameter to AUTO

Create an Undo Tablespace

CREATE UNDO TABLESPACE

You must also set the UNDO_TABLESPACE initilization parameter

To size the undo tablespace, you need to know how long the data is kept for: UNDO_RETENTION parameter
as well as how much undo is generated per second.

Find the undo rate:

You can use the dynamic performance view v$UNDOSTAT

If you recieve the error: “Snapshot too old”, increasing the retention peroid will help.
Retention is set to 30 seconds by default which is nrmally long enough but you can set this higher if necessary.

Categories: Oracle Basics Tags: Tags: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *