Re: Undo Tablespace growing even with retention = 0

>> Why would you expect behaviour to be different? Setting retention to 0
>> is more likely to guarantee an ORA-01555 than anything else. A 4MB UNDO
>> tablespace is preposterously small. What is the point of what you are
>> doing? Is there a business case hidden here somewhere or are you just
>>playing around for purposes of personal education?
>> --
>> Daniel A. Morgan

If you run the same example with 9i then the output would be different.

 31 ----
 32 end;
 33 /

• Before Starting Undo Size : 1
• Undo Size with undo_retention=0 : 1 <<----- Here it is 1 mb
• Undo Size with undo_retention=9000 : 213

PL/SQL procedure successfully completed.

This is was the answer I was expecting because I am commiting after each single update and undo_retention is set to 0.

There is no point for oracle to not to overwrite the existing space as record is commited and I do not
ask to keep this undo information for other queries [ undo_retention=0]

In 10g, Result is different because of it's new feature "Automatic Undo Tuning" ..
Oracle is ignoring my undo_retention, you can query tuned_undoretention column for what undo_retention was used at that time.

And yes, It is for personal education because I feel comfort myself with these type of small test casese to understand the concept rather than to keep remember the stuff.

Regards,
Jagjeet Singh Received on Mon Oct 09 2006 - 03:16:26 CDT