Got ORA-01555, but why my UNDOTBS (with autoextend set) is not growing???
We have a 9i database, recently changed from traditional rollback segments to AUM, with undo_retention set to 7200, and the UNDOTBS is set with 2 15G files, each file can grow to 32G.
After switching to AUM, I noticed that I often get ORA-01555, vs rollback segments did NOT give me any.Checking on the size of the UNDO tablespace, data files are not growing.
I noticed that one ORA-01555 indicates that the query runs 7962 seconds, so if my undo_retention is set to 7200, it will fail even if I still have plenty of space on Undo???
For other ORA-01555 messages, mostly the query run time is very short or even 0 seconds, I did some analysis, and find out that there are two jobs from other databases are calling the same MV refresh at the same time, I believe that cause the contention of UNDO, I will change the job schedule to see if I still get thise error in this case, but my question is how can I prove I have UNDO contention when two MV refresh against the same view/table run, and again, why the UNDO tbs is NOT growing??? Since rollback segment is
After switching to AUM, I noticed that I often get ORA-01555, vs rollback segments did NOT give me any.Checking on the size of the UNDO tablespace, data files are not growing.
I noticed that one ORA-01555 indicates that the query runs 7962 seconds, so if my undo_retention is set to 7200, it will fail even if I still have plenty of space on Undo???
For other ORA-01555 messages, mostly the query run time is very short or even 0 seconds, I did some analysis, and find out that there are two jobs from other databases are calling the same MV refresh at the same time, I believe that cause the contention of UNDO, I will change the job schedule to see if I still get thise error in this case, but my question is how can I prove I have UNDO contention when two MV refresh against the same view/table run, and again, why the UNDO tbs is NOT growing??? Since rollback segment is
0