Home » RDBMS Server » Server Administration » Plenty of UNDO but keep getting ORA-01555 (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0)
Plenty of UNDO but keep getting ORA-01555 [message #678194] Fri, 15 November 2019 10:23 Go to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
the process has been running without any issue in the past and just recently we are seeing this error "ORA-0155: snapshot too old". happens 3-4 times a week. we observe that after the process runs in 6 minutes it throws the error.


the SQL "insert into select ... from ..." process rows approximately 20k.

we further checked that the system parameter UNDO_RETENTION = 50000
undo tablespace UNDOTBS1
free blocks 155866.50 mb
used blocks 3112.50 mb
total blocks 158979.00 mb

further check on the alert log:
ORA-01555 caused by SQL statement below (SQL ID: cdzuqbmrn3kng, Query Duration=150 sec, SCN: 0x0d7f.ad3e4bfd):

there is plenty of undo and we think that will not be the issue. we are still trying to figure out what might be causing the issue. please advise.

thank you,
warren

[Updated on: Fri, 15 November 2019 12:17]

Report message to a moderator

Re: Plenty of UNDO but keep getting ORA-01555 [message #678196 is a reply to message #678194] Fri, 15 November 2019 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
we are still trying to figure out what might be causing the issue.

[Answer deleted due to lack of feedback in previous topics.]

Re: Plenty of UNDO but keep getting ORA-01555 [message #678199 is a reply to message #678196] Fri, 15 November 2019 16:35 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
Most likely cause is flawed application design/implementation due COMMIT inside LOOP
 
Re: Plenty of UNDO but keep getting ORA-01555 [message #678217 is a reply to message #678199] Mon, 18 November 2019 07:14 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
thanks blackswan. according to our app developer they do not have a loop.

it's a simple insert into table select … inside a package.

INSERT INTO table
SELECT …

the commit is outside the package.

the exact error that we are seeing was
ORA-01555: snapshot too old: rollback segment number 59 with name "_SYSSMU59_3745028836$" too small


the problem was sporadic there was no exact time and day when it will occur.

[Updated on: Mon, 18 November 2019 07:30]

Report message to a moderator

Re: Plenty of UNDO but keep getting ORA-01555 [message #678218 is a reply to message #678217] Mon, 18 November 2019 07:59 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923
Re: Plenty of UNDO but keep getting ORA-01555 [message #678221 is a reply to message #678218] Mon, 18 November 2019 09:30 Go to previous message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
thanks. could it be also related to autoextend on the datafiles is not set? i see that the autoextend is not on for the undo tablespace UNDOTBS1.
Previous Topic: Database upgrades - with No downtime
Next Topic: Location of alert log file
Goto Forum:
  


Current Time: Mon Dec 09 09:00:03 CST 2019