OraFAQ Forum: Server Administration » ORA-01555 caused by sql statement

Home » RDBMS Server » Server Administration » ORA-01555 caused by sql statement (9.2.0.8)

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

ORA-01555 caused by sql statement [message #390252]

Thu, 05 March 2009 11:54

Monica
Messages: 37
Registered: February 2000

Member

I have searched the internet and can't tell if the users query fails when I see the message in the alert log.
The query is coming from a user running a report in business objects. What user and what query/report I can't tell.

I have been going through the audit logs in Business Objects, and I am not finding report failures corresponding to the timestamp in the alert.log which makes me think maybe the query completes even though I receive the error message.

Monica

Report message to a moderator

Re: ORA-01555 caused by sql statement [message #390254 is a reply to message #390252]

Thu, 05 March 2009 11:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have searched the internet and can't tell if the users query fails when I see the message in the alert log.
You should have searched this forum.

Yes, query fails.

visit http://asktom.oracle.com & do keyword search on ORA-01555

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Report message to a moderator

Re: ORA-01555 caused by sql statement [message #390255 is a reply to message #390252]

Thu, 05 March 2009 11:59

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

ORA-01555:	snapshot too old: rollback segment number string with name "string" 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

May be long running query's; You don't have sufficient values in Undo Retention..

Before increasing your undo retention try to analyze your sql query..

Report message to a moderator

Re: ORA-01555 caused by sql statement [message #390258 is a reply to message #390254]

Thu, 05 March 2009 12:03

Monica
Messages: 37
Registered: February 2000

Member

I think the sql query needs to be looked at. I can't find the query causing the problem. I know it is coming from a user of business objects. When the error ORA-01555 caused by sql statement..... occurs in the alert.log, does the query fail? I am not hearing anything from the users of queries/reports failing in business objects. The logs don't tell me anything either.

Report message to a moderator

Re: ORA-01555 caused by sql statement [message #390261 is a reply to message #390252]

Thu, 05 March 2009 12:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The SELECT which is failing is the innocent victim.
It is SELECTing rows from a table while another session is doing DML against same table.

Report message to a moderator

Re: ORA-01555 caused by sql statement [message #390262 is a reply to message #390258]

Thu, 05 March 2009 12:06

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

??

Have you checked previous post from me & BlackSwan

Report message to a moderator

Re: ORA-01555 caused by sql statement [message #390269 is a reply to message #390252]

Thu, 05 March 2009 12:27

Monica
Messages: 37
Registered: February 2000

Member

I just got the error again. This is a datawarehouse populated by ETL. Users have read access only. I checked and the ETL is NOT running. Do you know if the user running the query/report will get an error? No one is complaining.

What makes you certain about DML running on the same table? What am I missing?

I am getting these errors 5 or 6 times a day in the alert log.

Any insight is greatly appreciated.

Monica

Report message to a moderator

Re: ORA-01555 caused by sql statement [message #390272 is a reply to message #390252]

Thu, 05 March 2009 12:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> Do you know if the user running the query/report will get an error?
Whether end user sees error or not is application dependent.

>What makes you certain about DML running on the same table?
From experience of having solved this many, many times.

>What am I missing?
DML generates UNDO.
ORA-01555 reports running out of UNDO.

>I checked and the ETL is NOT running
It could that ETL is part of the problem & aborting with this error.

One COMMON cause is within PL/SQL loop there is a COMMIT.
Who wrote the ETL s/w?

Report message to a moderator

Previous Topic:	Adding datafile in ASM
Next Topic:	Java pool& larg pool

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 03:33:33 CDT 2024