My experience with ora-01555
I have a large (3 TB) data warehouse, with a motley rabble of end users unleashing horrible queries at it all day. We are on v. 10.2.0.3.
Auto undo management is one of the best things Oracle ever rolled out. I admit to being skeptical at first, but I tried it (in 9i), and have never regretted that decision.
HOWEVER:
Considering the apocalyptic queries dispatched by our users, I have set the undo retention to 24000; that's right, 24000, as in 6.666666 hours. The undo tablespace itself has one 30G file.
We very rarely experience an ora-01555 error. When we get one, it is usually traced to a really bad query that would never complete, even if we had a 30 terabyte undo tablespace and unlimited retention. So Oracle puts the offending query out of its misery; a fitting end to a wasted life.
Oracle onlines and offlines the undo segments as needed, and does a good job of managing the undo space.
Here is a link to a website that pasted an Oracle Bulletin on undo management into a word document. You may find it useful.
http://www.globleat.com/articles/ora...0and%20sol.doc