Home » RDBMS Server » Server Administration » error ORA-01555 in Oracle 10g (Oracle 10g 10.2.0.3 ON LINUX 64 BIT)
error ORA-01555 in Oracle 10g [message #429500] Wed, 04 November 2009 04:43 Go to next message
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
Re: error ORA-01555 in Oracle 10g [message #429504 is a reply to message #429500] Wed, 04 November 2009 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: error ORA-01555 in Oracle 10g [message #429510 is a reply to message #429504] Wed, 04 November 2009 05:10 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Current size of my undo tablespace is 615 mb and currently it is 608mb is empty, should i keep this size
Re: error ORA-01555 in Oracle 10g [message #429524 is a reply to message #429510] Wed, 04 November 2009 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
currently it is 608mb is empty

Prove it!
Use a guarantee undo retention time in this case.

Regards
Michel
Re: error ORA-01555 in Oracle 10g [message #429868 is a reply to message #429500] Fri, 06 November 2009 00:57 Go to previous message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi,

Run the following query :-

SQL> select max(maxquerylen) from v$undostat;

You can get the longest query which ran for the instance.
Previous Topic: CPU utilization percentage by oracle Service
Next Topic: 9i to 10g upgrade - Materialized views
Goto Forum:
  


Current Time: Fri Dec 06 14:44:41 CST 2024