ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14$"

[Nikee]

Dear Friends,

I am facing the following error:

"ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14$" too small"

Here are the system and other necessary details...

Database: Oracle 10204 64-Bit
OS: SUSE LINUX SLES 10 64-bit X86

SQL> show parameters undo

NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 4200
undo_tablespace string UNDOTBSP


The size of the UNDO Tablespace: 40GB (10GB files a total of 4)

TableSpace Size (kb) Free (kb) Used % Free %
--------------- ------------ ------------ ------ ------------
UNDOTBSP 40,960,000 39,056,192 5 95

Max. Bytes/Minutes Avg. Bytes/Minutes Max Space (Mb) Avg Space (Mb)
------------------ ------------------ -------------- --------------
31,291,802 8,035,427 2,089 536


I still dont understand that, even though we have allocated such a huge space to UNDO tablespace, and it is not even using it 100%..why is the system is throwing the error...

Thank you,
Nikee

[Thomas7]

May be try seeing the v$undostats performance view, this gives the undo generated in 10min window interval. So that this give you more detailed info.

[davey23uk]

your undo retention isn't long enough for that query

[PAVB]

your undo retention isn't long enough for that query

Dave is correct - as he is most of the time.

Check alert log, Oracle wrote down for how long query ran before abend... bet you find query ran for more than 4200 seconds.