Home » RDBMS Server » Server Administration » How to investigate this ORA-01555 (9.2.0.6)
How to investigate this ORA-01555 [message #314148] Tue, 15 April 2008 21:45 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
I am using EBusiness Suite 11i and recently i saw ORA-01555 in the alert log file.
In this particular situation, the error message is not followed by the SQL Query.
How do i investigate this??

Section of alert log:

Tue Apr 15 15:40:07 2008
ORA-01555 caused by SQL statement below (Query Duration=1208256007 sec, SCN: 0x0001.805a7901):
Tue Apr 15 15:40:07 2008
table_4_200_3f11675_0_0_0
ORA-01555 caused by SQL statement below (Query Duration=1208256008 sec, SCN: 0x0001.805a7901):
Tue Apr 15 15:40:08 2008
table_4_200_3f11675_0_0_0
ORA-01555 caused by SQL statement below (Query Duration=1208256008 sec, SCN: 0x0001.805a7901):
Tue Apr 15 15:40:08 2008
table_4_200_3f11675_0_0_0
ORA-01555 caused by SQL statement below (Query Duration=1208256015 sec, SCN: 0x0001.805a7901):
Tue Apr 15 15:40:15 2008
table_4_200_3f11675_0_0_0
ORA-01555 caused by SQL statement below (Query Duration=1208256015 sec, SCN: 0x0001.805a7901):
Tue Apr 15 15:40:15 2008
table_4_200_3f11675_0_0_0
ORA-01555 caused by SQL statement below (Query Duration=1208256015 sec, SCN: 0x0001.805a7901):
Tue Apr 15 15:40:15 2008
table_4_200_3f11675_0_0_0
ORA-01555 caused by SQL statement below (Query Duration=1208256016 sec, SCN: 0x0001.805a7901):
Tue Apr 15 15:40:16 2008
table_4_200_3f11675_0_0_0



Regards,
sandi
Re: How to investigate this ORA-01555 [message #314191 is a reply to message #314148] Wed, 16 April 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1555 is the most common error, google for it.

Regards
Michel
Re: How to investigate this ORA-01555 [message #314194 is a reply to message #314191] Wed, 16 April 2008 00:58 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi Michel,
I know about ORA-01555, but normally i used to get the problem query below the ORA code in the alert log. But in this case, only table_4_200_3f11675_0_0_0 is written in the alert log
Re: How to investigate this ORA-01555 [message #314211 is a reply to message #314194] Wed, 16 April 2008 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a read on a lob segment whose object_id in 200 in hexadecimal (i.e. 512).

Regards
Michel
Re: How to investigate this ORA-01555 [message #314213 is a reply to message #314194] Wed, 16 April 2008 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry took the wrong part it is object_id=3f11675

Regards
Michel
Re: How to investigate this ORA-01555 [message #314309 is a reply to message #314148] Wed, 16 April 2008 05:51 Go to previous messageGo to next message
pokhraj_das
Messages: 64
Registered: February 2008
Member

I think it should be better to run the following query:-
=====================================
select max(maxquerylen) from v$undostat;

It will tell the longest query run for the last 24 hours. from that you can make an idea your undo_retention value...


pokhraj
Re: How to investigate this ORA-01555 [message #314319 is a reply to message #314309] Wed, 16 April 2008 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Useless in case of LOBs which do not use undo segments.

Regards
Michel
Re: How to investigate this ORA-01555 [message #314351 is a reply to message #314148] Wed, 16 April 2008 07:40 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
If it is not using Undo segments, then why is it throwing ORA-01555
Re: How to investigate this ORA-01555 [message #314360 is a reply to message #314351] Wed, 16 April 2008 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it does not find a previous version of your LOB in the same segment.
LOB and other datatypes are not handle in the same way regarding consistent reads.

Regards
Michel
Re: How to investigate this ORA-01555 [message #314365 is a reply to message #314148] Wed, 16 April 2008 08:21 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thanks Michel,
In one of your previous updates of this topic, you told that the object is related to OBJECT_ID=512

When I query ALL_OBJECTS for OBJECT_ID=512,
it corresponds to AUX_STATS$

SQL> DESC AUX_STATS$
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SNAME                                                 NOT NULL VARCHAR2(30)
 PNAME                                                 NOT NULL VARCHAR2(30)
 PVAL1                                                          NUMBER
 PVAL2                                                          VARCHAR2(255)



Regards,
Sandi
Re: How to investigate this ORA-01555 [message #314366 is a reply to message #314365] Wed, 16 April 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I corrected that in the next message saying I took the wrong part and the object id was 3f11675.

Regards
Michel
Re: How to investigate this ORA-01555 [message #314545 is a reply to message #314148] Thu, 17 April 2008 02:20 Go to previous message
pokhraj_das
Messages: 64
Registered: February 2008
Member

Try this command:-
====================

1. ALTER SYSTEM SET EVENTS '1555 TRACE NAME ERRORSTACK LEVEL 4';
2. Wait till the error reproduce.
3. A trace file will generate for every occurance of OEA01555 erorr.

Thanx
pokhraj


Previous Topic: unset session parameter during runtime of session
Next Topic: ORA-01034: Oracle Not Available (merged)
Goto Forum:
  


Current Time: Mon Dec 05 03:14:00 CST 2016

Total time taken to generate the page: 0.06051 seconds