| Plenty of UNDO but keep getting ORA-01555 [message #678194] |
Fri, 15 November 2019 10:23  |
wtolentino
Messages: 421
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 #678217 is a reply to message #678199] |
Mon, 18 November 2019 07:14  |
wtolentino
Messages: 421
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
