This discussion is archived
5 Replies Latest reply: Sep 10, 2013 2:47 AM by Aman.... RSS

Retention Guarantee

Kamesh Newbie
Currently Being Moderated

Dear Experts,

 

I have installed oracle database 11g rel 2  on win 7 machine.

 

The total size of undo tablespace is 75 MB and currently used is 17 MB and has 58 MB free space.

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------

undo_management                      string      AUTO

undo_retention                            integer     900

undo_tablespace                         string      UNDOTBS1

 

My unserstanding:

 

 

Currently 17 MB "commited" data can be retained before it is over written in 15 minutes.

1) Is my understanding correct ?

 

 

The oracle document says

(http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo002.htm)

 

"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."

 

2) If there is'nt enough space in undo tablespace,how is it possible for oracle to keep the unexpired undo data available?

 

3)the oracle document says

 

Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

 

i did not get the above statement. can you please make me clear.

 

 

 

Best Regards,

Kamesh

  • 1. Re: Retention Guarantee
    rp0428 Guru
    Currently Being Moderated

    Currently 17 MB "commited" data can be retained before it is over written in 15 minutes.

    1) Is my understanding correct ?

    No - look at this again

    The total size of undo tablespace is 75 MB

    How much data can be in UNDO?

    Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

     

    i did not get the above statement.

    What part of the following statement are you having problems with?

    the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

    If the UNDO space is used by 'committed' data that is part of the 'guarantee' then there is NO (or not enough) space for new DML transactions so they will fail.

    2) If there is'nt enough space in undo tablespace,how is it possible for oracle to keep the unexpired undo data available?

    Because it is already IN the UNDO tablespace. All that Oracle has to do to 'keep it available' is to not reuse that space for NEW transactions.

     

    1. You have a freezer that will hold 200 pounds of meat.

    2. The freezer is currently empty.

    3. Any meat you put in the freezer is 'guaranteed' to be good for 60 days.

    4. Today you put 150 pounds of meat in the freezer and you put a label on it with today's date.

    5. How much meat can you put in the freezer tomorrow if you don't throw any of the existing 150 pounds of meat away?

     

    Did you guess 50 pounds? Congratulations.

     

    Until meat has been in the freezer for 60 days you can't throw it away to make room for new meet.

     

    So for 60 days the most new meat you can put in the freezer is 50 pounds since the freezer only holds 200 pounds and you already have 150 pounds in it.

     

    If you try to put MORE than 50 pounds in the freezer you will FAIL; it won't fit.

     

    1. After sixty days you try to put 100 pounds of meat in the freezer

    2. There is not enough room in the freezer

    3. But there is 100 pounds of meat that is over 60 days old

    4. You throw the old 100 pounds of meat away and use that space for the new 100 pounds of meat.

     

    (please don't donate that old, rancid 100 pounds of meat to a homeless shelter - they deserve better than that!)

  • 2. Re: Retention Guarantee
    Aman.... Oracle ACE
    Currently Being Moderated

    Oracle would keep the old data , after the transaction gets committed for 900 seconds and if you have set the retention guarantee than Oracle would "ensure" that for this time period, the kept Undo data won't be overwritten, even at the cost of failiing new transactions. That's all, in a summarized form , your entire  question's answer is. Now, tell us where do you still have doubts and we shall try to clear them.

     

    Aman....

  • 3. Re: Retention Guarantee
    rp0428 Guru
    Currently Being Moderated

    Buy a bigger freezer?

  • 4. Re: Retention Guarantee
    Kamesh Newbie
    Currently Being Moderated

    Thanks Aman and rp0428 .

     

    rp0428 - Your explanation was simply amazing.

  • 5. Re: Retention Guarantee
    Aman.... Oracle ACE
    Currently Being Moderated

    rp0428 wrote:

     

    Buy a bigger freezer?

    Yep and if can't buy , use  someone else's freezer err Cloud Services .

     

    Regards

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points