Home » RDBMS Server » Server Administration » ORA-01555 caused by sql statement (9.2.0.8)
ORA-01555 caused by sql statement [message #390252] Thu, 05 March 2009 11:54 Go to next message
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
Re: ORA-01555 caused by sql statement [message #390254 is a reply to message #390252] Thu, 05 March 2009 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: ORA-01555 caused by sql statement [message #390255 is a reply to message #390252] Thu, 05 March 2009 11:59 Go to previous messageGo to next message
babuknb
Messages: 1734
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..
Re: ORA-01555 caused by sql statement [message #390258 is a reply to message #390254] Thu, 05 March 2009 12:03 Go to previous messageGo to next message
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.
Re: ORA-01555 caused by sql statement [message #390261 is a reply to message #390252] Thu, 05 March 2009 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: ORA-01555 caused by sql statement [message #390262 is a reply to message #390258] Thu, 05 March 2009 12:06 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


??

Have you checked previous post from me & BlackSwan
Re: ORA-01555 caused by sql statement [message #390269 is a reply to message #390252] Thu, 05 March 2009 12:27 Go to previous messageGo to next message
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
Re: ORA-01555 caused by sql statement [message #390272 is a reply to message #390252] Thu, 05 March 2009 12:36 Go to previous message
BlackSwan
Messages: 25046
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?
Previous Topic: Adding datafile in ASM
Next Topic: Java pool& larg pool
Goto Forum:
  


Current Time: Thu Dec 08 02:04:33 CST 2016

Total time taken to generate the page: 0.15733 seconds