Kamran Agayev's Oracle Blog

Oracle Certified Master

Chapter 10 – Managing the Undo Tablespace

Posted by Kamran Agayev A. on December 1st, 2009

–          Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo

–          Undo records are used to L:

  • Rollback transactions when a ROLLBACK statement is issued
  • Recover the databsae
  • Provide read consistency

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

–          In automatic undo management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions

–          When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.

Undo Retention

–          After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

–          When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Dataabase attempts to retain old undo information before overwriting it. Old (commiteed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.

–          Oracle database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the databsae begins to overwrite expired undo. If the undo tbalespace has no space for new transactions after all expired undo is overwritten, the databsae may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

–          To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed, the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

–          You enable retention guarantee by specifying the RETENTIN GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement.

8 Responses to “Chapter 10 – Managing the Undo Tablespace”

  1. Pavan Kumar N Says:


    Today I have gone through the profile of yours and find the interesting carrer record and surprized to your DBO. You are on 17th Dec and I am on 19th Dec.

    Lucky guys…!!
    Take care

    – Pavan Kumar N

  2. Kamran Agayev A. Says:

    Hi Pavan. Nice to meet you here.

    You’ve found an interesting coincidence :)

  3. Pavan Kumar N Says:


    I thought of go through your book, which you have written, but could not able to find the book in amazon or any other websites to purchase or atleast a pdf copy to review and I am looking forward to write an article.

  4. Kamran Agayev A. Says:

    Dear Pavan

    The book hasn’t published yet and we’re currently working on it
    It will be available for the second half of the next year (2010)

  5. Neha Malik Says:

    Hey Mr. Kamran,

    I saw the book link that you and Mr. Aman Sharma are writing. Good luck to you. Will this book be available in India as well? Google searching lead me to your book. I saw your and Mr. Sharma’s threads on ora forums as well. Please let me know when you guys would be ready with the book and its availability in india.

    PS: A suggestion if you won’t mind. I noticed you write “Author” in your thread signs when it should be “co-author” as you both are involved. Here also you should mention the same. I hope you won’t mind my suggestion. Thank you.

  6. Kamran Agayev A. Says:

    Dear Neha

    This book will be finished for the second half of the next year and you’ll be able to buy it online

    P.S. Regarding your suggestion, for one book there’s one author, and one co-author. So here, I’m author and Aman is co-author, however we’re both working on it (and Aman knows more than me :) )
    “Author” or “co-author” are just namings, nothing else

  7. Pavan Kumar N Says:

    Hi Kamran,

    Wish a you many many Happy Returns of the Day…!!
    I wish that you dreams should come true in life… All the Best
    Take care and Have a Great Day.. Ahead…!!
    I was looking in forums to wish there. I was busy thorough out the day with attending interviews so I was a bit to convey my wishes.

  8. Kamran Agayev A. Says:

    Thank you Pavan. I really appreciate it. Thank you for your wishes my dear friend
    Wish you the same

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>