This content has been marked as final. Show 12 replies
ORA-01555 frequently caused by COMMIT within PL/SQL LOOP
I didnt get you , can you please tell me in details, thanks for your respnose.
http://asktom.oracle.com1 person found this helpful
do a keyword search for "ORA-01555"
I went with several documents including asktom's articles & http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/undo004.htm#BABJCFFE
SQL> select max(MAXQUERYLEN) from v$undostat;
undo retention is 4000 so if we increase undo_retention more than longest running query can we avoid ORA-01555 SNAPSHOT too old errors.
undo tablespace is autoextend on, and the undo tablespace is around 35GB, even not using atleast 60% of the UNDO, so is it better to extend undo_retention > 5100 ?
Can anyone give suggestion that if we increase undo retention > 5100 , can overcome this problem?
Thanks in advance.
Edited by: CKPT on Nov 11, 2010 1:24 AM
I recommend :1 person found this helpful
1. to set UNDO_RETENTION parameter to value greater than your longest running query
2. to have a fixed size undo tablespace
3. to monitor ORA-01555 with instance alert log and V$UNDOSTAT.SSOLDERRCNT
4. if possible try to tune the longest running queries
Thanks for your reply.
1. to set UNDO_RETENTION parameter to value greater than your longest running queryIts Ok , i will do
2. to have a fixed size undo tablespaceNo, do you mean autoextensible is off? no undo tablespace is autoextensible
3. to monitor ORA-01555 with instance alert log and V$UNDOSTAT.SSOLDERRCNTSQL> select MAXQUERYLEN,SSOLDERRCNT from v$undostat where MAXQUERYLEN>5000;
4. if possible try to tune the longest running queriesIt is materialized view.. i will do
Seeking more help Thanks :)
Edited by: CKPT on Nov 11, 2010 1:51 AM
Any suggestions please.. Thanks :) :)
2. to have a fixed size undo tablespacedo you mean undo tablespace autoextensible need to "OFF" ? Thanks
Yes - fixed means do not set the UNDO tablespace to autoextend.1 person found this helpful
See if these MOS Docs can help
262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
269814.1 - ORA-01555 Using Automatic Undo Management - Causes and Solutions
I believe your maxquerylen value is increasing in a pase; as initially you had posted it as 1400 and now it has crossed 5000.
I think its better for you to set undo_retention to a value of 7000+ and monitor continously.
Btw: Make sure UNDO tbs has enough free space as well.
If maxquerylen value is increasing; you have to keep on increasing the undo_retention and decide accordingly.
By the way; it would be always better if you could fine tune the problematic queries.
I am not sure - but I dont think it would be a better option to turn OFF the autoextend mode of UNDO tbs as you are facing the 1555.
I am thinking case of "Delayed Blocked Clean outs"
Can you check for any existence of LOBS @ segment level.
- Pavan Kumar N
Increasing the undo retention is the fix for the issue. On doing what did u encounter this error. If it is during an import(nw link) then it is due to intensive data loading in the source during the time of the import.