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!)
1 person found this helpful
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.
Buy a bigger freezer?
Thanks Aman and rp0428 .
rp0428 - Your explanation was simply amazing.
Buy a bigger freezer?
Yep and if can't buy , use someone else's freezer err Cloud Services .