OraFAQ Forum: Server Administration » How to investigate this ORA-01555

Home » RDBMS Server » Server Administration » How to investigate this ORA-01555 (9.2.0.6)

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

How to investigate this ORA-01555 [message #314148]

Tue, 15 April 2008 21:45

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

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314191 is a reply to message #314148]

Wed, 16 April 2008 00:45

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

Senior Member
Account Moderator

1555 is the most common error, google for it.

Regards
Michel

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314194 is a reply to message #314191]

Wed, 16 April 2008 00:58

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

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314211 is a reply to message #314194]

Wed, 16 April 2008 01:36

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314213 is a reply to message #314194]

Wed, 16 April 2008 01:42

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

Senior Member
Account Moderator

Sorry took the wrong part it is object_id=3f11675

Regards
Michel

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314309 is a reply to message #314148]

Wed, 16 April 2008 05:51

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

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314319 is a reply to message #314309]

Wed, 16 April 2008 06:11

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

Senior Member
Account Moderator

Useless in case of LOBs which do not use undo segments.

Regards
Michel

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314351 is a reply to message #314148]

Wed, 16 April 2008 07:40

sandhyaa
Messages: 79
Registered: February 2007

Member

If it is not using Undo segments, then why is it throwing ORA-01555

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314360 is a reply to message #314351]

Wed, 16 April 2008 08:03

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314365 is a reply to message #314148]

Wed, 16 April 2008 08:21

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

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314366 is a reply to message #314365]

Wed, 16 April 2008 08:28

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: How to investigate this ORA-01555 [message #314545 is a reply to message #314148]

Thu, 17 April 2008 02:20

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 06 16:37:56 CDT 2024