ORA-01555 - 9i Automatic Undo Management

[Nikee]

Hello,

We are running on Oracle 9i and also we implimented all the 9i features.

3 days back we ran into the following error:

ORA-01555 caused by SQL statement (Query Duration=4736 sec, SCN: 0x0000.235fde04):

I am sure the above error is a snapshot too old error, but I need your help in resolving this issue. Here are some details:

NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string PSAPUNDO


TABLESPACE SPACE FREE USEDSPACE FREESPACE
----------- ---------- ---------- ---------- ----------
PSAPUNDO 12288000 12249536 0 100



TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT
---------------- --------- --------- ---------- --------- ------ -------------- -----------
PSAPUNDO ONLINE UNDO LOCAL SYSTEM MANUAL 65536

Error:=====>

Begin End UndoBlocks ORA-1555
------------------- ------------------- ---------- ----------
2004-06-13 23:16:57 2004-06-13 23:26:57 1137 1


--Maximum value for UPS
Max. Undo Blocks/Sec
--------------------
42.3066667


--Average value for UPS:
AVG. Undo Blocks/Sec
--------------------
2.49352273


Max. Query Length Sec
---------------------
4736

Undo Sp Req.(MB)
----------------
297


I hope I have included most of the details related to the error. Please let me know what settings will prevent the
ORA-01555 error in the future.

Thanks & regards,
Nikee

[julian]

You had a query that took 4736 seconds, and your UNDO_RETENTION is 900 seconds. If you increase it to 5000, you will not probably see that error but can you afford to have so big UNDO tablespace? You will need in this case to increase the size of the UNDO tablespace.