Re: Undo Tablespace Question

This won't solve your problem. If the size of the undo tablespace is increasing beyond what you consider to be reasonable, you need to find out why. Obviously, this is a recurring issue, so there is a deeper problem.

Undo is generated by changes to data. What is generating these changes? You need to look into the application and the business to see if there are capacity issues (more activity than originally planned) or application issues (huge batch updates that commit very infrequently).

Case in point. An overnight batch process terminated with an error because it was unable to allocate additional undo space. We monitored the process the next night and saw that this process had allocated over 10 gigabytes of undo space (way too much for this process) before terminating. This was the result of an application change that took out the periodic commits (every 10,000 records) to a single commit when all changes had been made (in excess of 100 million records).

Unless you are using 'enforced' undo retention time, don't focus on undo as the issue. It is only the symptom.

Regards,
Daniel Fink Received on Tue Nov 22 2005 - 10:43:35 CST