Database Technologies‎ > ‎Oracle‎ > ‎

Undo Management

  1. Undo What is that
  2. Configuring Undo
  3. Sizing Undo Tablespaces
  4. Undo Advisor
    Undo, why undo ?
    We require undo for following
    Read Consistency :   Read consistency gives your query the output
    as it was at that point in time
    Example : Imagine you issued select query which takes 4-5 min to
    execute, and on the second minute someone modified the data by
    issuing update command, inspite of this the first query's output is
    unaffected. That is because the before modified image is stored
    in the undo
    Rollback Transaction :  To undo any modified data Example suppose
    you have modified any data and then you change mind not to modify
    the data, then you could undo that.

    When a rollback statement is issued, undo records are used to undo changes that were made to the
    database by uncommited transaction.
    Recover Database :   During recovery undo records are used to undo
    any uncommited changes applied from the redo logs to the datafiles.
    Flashback Query :   Here you could get the image of the database as
    it was before some time. say 15-20 minutes if undo is configured properly
    Configuring Undo
    We have three imp undo parameter for configuration
    undo_management  = auto
    undo_tablespace = <undo tablespace name>
    undo_retention = <time in seconds>
    Undo Management :  Setting it to auto lets oracle manage the undo
    Undo Tablespace   :   Undo tablespace which will be active.
    Undo retention         :  Time in seconds til which oracle will store the undo
                                            information in tablespace.
    Sizing Undo Tablespaces
    Oracle provides tool v$undostat, it stores undo block generation data,
    which are taken at 10 minutes interval. In the v$undostat table you
    would find column undoblks which is in bytes.
    Two parameter vital for preventing errors related to undo are

    1) undo retention
    2) Undo tablespace size

    SQL>select max(maxquerylen) from v$undostat;

    above query gives us the undo retention, now we have to calculate the
    size of undo

    For getting the undo size we again query the v$undostat, which
    store the undo blocks generated in 10 minutes interval

    Note :  v$undostat keeps 4 days of information


    undo size = transaction rate * block size * undo retention

    select max(undoblks) from v$undostat;

    transaction rate = [select max(undoblks) from v$undostat;]  /  600

    Now undo size = transaction rate * block size * undo retention

    Monitor Undo status
     select status , sum(blocks)*8192/1024/1024/1024 GB from dba_undo_extents group by status; 
     Additional Details
    Each transaction is assigned to only one undo segment,however one undo segment can be
    assigned to multiple transaction at a given point of time, to check which transaction have
    which undo segments assigned query the v$transaction view.
    Undo segments expand and contract automatically as needed and act as circular storage
    buffer for transactions
    Transaction fills extents in its undo segment until a transaction is completed or all space is
    When an extend fills up and more space is needed, the transaction will acquire more space
     from next extent in the segment.
    When all extent in the undo segment are consumed, the transaction overwrites undo data
    which is no longer needed. Undo tablespace contain undo segments, an instance can
    have only  one active undo tablespace at a time
    Undo segments are owned by SYS and they have segment type "TYPE 2 UNDO"
    Undo tablespace are specialized, permanent tablespace with automatic
    extent allocation.
    There are only two this one needs to know to monitor undo
    1) Undo space
    2) Snapshot to old error
    UNDO Space : when huge transaction takes place in the database, this requires huge space
                                in the undo tablespace to store the original data
                            insert operation requires very little undo space
                            delete operation requies huge undo space
                            when undo tablespace runs out of space, the oracle server raises ORA-01650
                            unable to extend rollback segment

    Snapshot to old error : Here when a query tries to access data that no longer exists in the
    undo tablespace, this happens when a query takes long time to execute, here we get ORA-1555
    snapshot too old error

    Undo management.
    Two Types Of Undo management
    Automatic : Here the original data is stored in undo segment
                         Oracle server determines the time period for which the undo
                        data is retained in undo segments, data is stored in the undo
                        segments even after the transaction is complete (called unexpired data)
    Undo retention  :  Amount of time for which the commited undo data should be retained in the
    undo tablespace, after which it will expire or will be overwritten. In Automatic undo management
    undo is retained till it expires, however if an active transaction required more space the
    commited but unexpired data is overwritten. If undo retention is set to 0, automatic undo retention
     tuning is enabled, this retains undo information as required to support longest running query
    Manual : The segment where the original data is stored is called as rollback segment
    Undo data falls in three categories

    1) Uncommited undo info  for active transaction, used for rollback or recovering failed transaction,
         this is never overwritten
    2) Commited undo   : These is retained to meed the retention period

    3) Expired undo  : these is not required to support an active transaction and is overwritten when
          an active transaction needs space and no free is available When space is required by active
          transaction and there is no free space Commited undo data is overwritten instead of failing
          the transaction. However if the retention_guarantee is set the, these would not happen and lets
          transaction to fail for lack of undo space.