From oracle-l-bounce@freelists.org Wed Oct 5 07:19:37 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j95CJaWe024760 for ; Wed, 5 Oct 2005 07:19:36 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j95CJWvX024740 for ; Wed, 5 Oct 2005 07:19:32 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E8E641F2741; Wed, 5 Oct 2005 07:18:46 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00326-04; Wed, 5 Oct 2005 07:18:46 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 628C01F1AEA; Wed, 5 Oct 2005 07:18:46 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=NfhAW6CxNCjBqX+ALYqJlZSo98f1id0Pr4FIf+foWwBAVknjF5/UHkJWxf7DxCBSDpPnvCgtUitTf2n+kD5H5b2LSBgCtLopkvuHLDXeuFflTxZQQWvMj8hJKZdadd5v5eve2PA3GaYGYnq3pa6GHCN7KL0Z81T9ewNIBy2tTCA= Message-ID: Date: Wed, 5 Oct 2005 20:16:45 +0800 From: Lou Fangxin To: zhuchao@gmail.com Subject: Re: How to find out the original SQL resulted the ERROR message? Cc: Oracle-L In-Reply-To: <962cf44b0510050359h20489cdaldb0688ab92474543@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27491_7986786.1128514605779" References: <962cf44b0510050350o2848f7cemd066e48654a7d4e8@mail.gmail.com> <962cf44b0510050359h20489cdaldb0688ab92474543@mail.gmail.com> X-archive-position: 26341 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: anysql@gmail.com Precedence: normal Reply-To: anysql@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.7 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE, UPPERCASE_25_50 autolearn=no version=2.63 ------=_Part_27491_7986786.1128514605779 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Try to query the v$sql_plan where other_tag is not null, this will list the query that parallel executed, then we need manually compare the sql. On 10/5/05, zhu chao wrote: > > Sorry, pressed enter before finish the email. > Sometimes we see some error message in alert, saying some SQL report > ORA-1555. For example, the following SQL is logged in alert. > Obviously it is not from normal application. I am wondering how I can fin= d > out the original SQL, so I contact with developer for this. > Another SQL like: > ARC1: Completed archiving log 4 thread 1 sequence 9781 Tue Oct 4 22:07:31 > 2005 > ORA-01555 caused by SQL statement below (SCN: 0x0000.e3a53f18): > Tue Oct 4 22:07:31 2005 > SELECT /*+ Q109000 NO_EXPAND ROWID(A1) */ A1.ROWID FROM "BES1"."EXCEPTION= " > PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1 WHERE > A1."CONSUMER_ID"=3D'KenanToSiteProcessor' > AND A1."CREATION_DATE">=3DTO_DATE('2005-09-29 00:00:00', 'yyyy-mm-dd > hh24:mi:ss') > AND A1."CREATION_DATE"<=3D:B1 AND A1."EXCEPTION_STATUS"=3D3 AND > A1."EXCEPTION_REASON" LIKE '3501 DATA_SOURCE_DOWN%' > Tue Oct 4 22:12:34 2005 > This is the spawned from some PQ query. If PQ process has exit, or not > running any more, can we still find out the original SQL? > Thanks > On 10/5/05, zhu chao wrote: > > > > Hi, All, > > > > ORA-01555 caused by SQL statement below (SCN: 0x0314.01fa70b6): > > Mon Oct 3 18:52:31 2005 > > SELECT /*+NESTED_TABLE_GET_REFS+*/ "FDBK_USER"."TRANSACT_RECORD".* FROM > > "FDBK_USER"."TRANSACT_RECORD" where trx_type=3D1 > > > > -- > > Regards > > Zhu Chao > > www.cnoug.org > > > > > > -- > Regards > Zhu Chao > www.cnoug.org > -- Welcome, Fangxin.Lou from China Home: http://www.anysql.net/en/ Mydul: http://www.anysql.net/en/mydmp.html ------=_Part_27491_7986786.1128514605779 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Try to query the v$sql_plan where other_tag is not null, this wil= l list the query that parallel executed, then we need manually compare the = sql.

On 10/5/05, = zhu chao <zhuchao@gmail.com= > wrote:
Sorry, pressed enter before finish the email.
 
Sometimes we see some error message in alert, saying some SQL report O= RA-1555. For example, the following SQL is logged in alert.
Obviously it is not from normal application. I am wondering how I can = find out the original SQL, so I contact with developer for this.
 <= /div>
Another SQL like:
ARC1: Completed archiving  log 4 thread 1 sequence 9781 Tue Oct&n= bsp; 4 22:07:31 2005
ORA-01555 caused by SQL statement below (SCN: 0x000= 0.e3a53f18):
Tue Oct  4 22:07:31 2005
SELECT /*+ Q109000 NO_EXPA= ND ROWID(A1) */=20 A1.ROWID FROM "BES1"."EXCEPTION" PX_GRANULE(0, BLOCK_RA= NGE, DYNAMIC)  A1 WHERE A1."CONSUMER_ID"=3D'KenanToSiteProce= ssor'
AND A1."CREATION_DATE">=3DTO_DATE('2005-09-29 00:00:= 00', 'yyyy-mm-dd hh24:mi:ss')=20
AND A1."CREATION_DATE"<=3D:B1 AND A1."EXCEPTION_STATU= S"=3D3 AND A1."EXCEPTION_REASON" LIKE '3501 DATA_SOURCE_DOWN= %'
Tue Oct  4 22:12:34 2005
This is the spawned from some PQ que= ry. If PQ process has exit, or not running any more, can we still find out = the original SQL?=20
 
Thanks
 
On 10/5/05, = zhu chao <zhuchao@gmail.com= > wrote: =20
Hi, All,

ORA-01555 caused by SQL statement below (SCN: 0x0314= .01fa70b6):
Mon Oct  3 18:52:31 2005
SELECT /*+NESTED_TABLE_GET_= REFS+*/ "FDBK_USER"."TRANSACT_RECORD".* FROM "FDBK= _USER"."TRANSACT_RECORD"  where trx_type=3D1=20
 

--
Regards
Zhu Chao
ww= w.cnoug.org
 


--
Regards
Zhu Chao
www.cnoug.org<= br>



--
Welcome= , Fangxin.Lou from China
Home: http://www.anysq= l.net/en/
Mydul: htt= p://www.anysql.net/en/mydmp.html
------=_Part_27491_7986786.1128514605779-- -- http://www.freelists.org/webpage/oracle-l