From oracle-l-bounce@freelists.org Fri Oct 15 10:44:32 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9FFiVe25066 for ; Fri, 15 Oct 2004 10:44:31 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9FFiTI25060 for ; Fri, 15 Oct 2004 10:44:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7DAA572D542; Fri, 15 Oct 2004 10:50:36 -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 31660-71; Fri, 15 Oct 2004 10:50:36 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D381472D563; Fri, 15 Oct 2004 10:50:25 -0500 (EST) DomainKey-Signature: a=rsa-sha1; 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:content-transfer-encoding:references; b=KRQRs25HCk4fjzh+fk8V8ckYSQmxwNJSGI7ENDExW8E/8qoM2a+jkrRd7I1cB2iSIBU8nnW1ECh+s0oClLWISMSqRXT6lYiSCLzA7X27bSrY3NHEAQKabEZ27VvL1ITrTYivQLm8H3I+HpVroMBSJLvLVoc3soHTuV8MlEN8rd8 Message-ID: <9177895d04101508474311e7e7@mail.gmail.com> Date: Fri, 15 Oct 2004 11:47:23 -0400 From: rjamya To: jreyes@dazasoftware.com Subject: Re: Unable to find SQL executed by blocking session Cc: "oracle-l@freelists.org" In-Reply-To: <416FEF32.000001.01756@DAZA-MGEJCA5J7T> Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit References: <200410151544.i9FFiXTm004817@webmail.nexlink.net> <416FEF32.000001.01756@DAZA-MGEJCA5J7T> X-archive-position: 11154 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rjamya@gmail.com Precedence: normal Reply-To: rjamya@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Thanks Juan,=20 to give you an idea, my script offers following output ... and I like what I am showing to app support personnel. It is partially based on Steve Adam's script. ** Active Locks in "RELNCS" ordered by RESOURCE ** INST RESOURCE SID S DB/OS User HOLDING WANTING LCKTIM HASH VALUE -------- -------------------- ----- - -------------------- ------- ------- ------ --------------- RELNCS1 TX-131116-329796 38 I TCS/ORACLEI X =20 105 0 RELNCS2 TX-131116-329796 35 A TCS/ORACLEI =20 X 100 1436257755 Resource BLOCKER "TCS/oraclei" is logged on "RELNCS1" and holding the lock for last "105" seconds." Partial SQL statement (hash value: 0) executed by the BLOCKER is as follows= ... Resource WAITER "TCS/oraclei" is logged on "RELNCS2" and waiting for the lock for last "100" seconds." Partial SQL statement (hash value: 1436257755) executed by the WAITER is as follows ... update raj_test set a =3D :"SYS_B_0" Database lock Info, ESPN Oracle Utilities, =A9 ESPN 2004 Elapsed: 00:00:00.28 SQL> exit=20 See that hash_value of 0 for BLOCKER? Tha'ts what my problem is. Cheers Raj -- http://www.freelists.org/webpage/oracle-l