OraFAQ Forum: Server Administration » error ORA-01555 in Oracle 10g

Home » RDBMS Server » Server Administration » error ORA-01555 in Oracle 10g (Oracle 10g 10.2.0.3 ON LINUX 64 BIT)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

error ORA-01555 in Oracle 10g [message #429500]

Wed, 04 November 2009 04:43

MIFI
Messages: 256
Registered: February 2008
Location: U.K.

Senior Member

Hi,
My UNDO_MANAGEMENT is AUTO and Tablespace size is Auto

Undo_retention=15

Today morning, I have received following error in Alert log
ORA-01555 caused by SQL statement below (SQL ID: 9yfaam0vn51b5, Query Duration=1257324485 sec, SCN: 0x0000.084ad6ea):

EM is showing following
Snapshot Too Old Error detected: SQL ID 9yfaam0vn51b5, Snapshot SCN 0x0000.084ad6ea, Recent SCN 0x0000.084e39df, Undo Tablespace UNDOTBS1, Current Undo Retention 943.

How do i find that which query created a problem as SQL ID: 9yfaam0vn51b5 is not in table v$sqlTEXT

Do i need to do anything or Oracle will take care of UNDO?

Regards

Report message to a moderator

Re: error ORA-01555 in Oracle 10g [message #429504 is a reply to message #429500]

Wed, 04 November 2009 04:53

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-01555: snapshot too old: rollback segment number %s with name \"%s\" too small
 *Cause: rollback records needed by a reader for consistent read are
           overwritten by other writers
 *Action: If in Automatic Undo Management mode, increase undo_retention
          setting. Otherwise, use larger rollback segments

Regards
Michel

Report message to a moderator