Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id g9AM0xw31652 for ; Thu, 10 Oct 2002 17:00:59 -0500 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id g9AM0wD31640 for ; Thu, 10 Oct 2002 17:00:58 -0500 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA61229; Thu, 10 Oct 2002 12:42:19 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004E62C1; Thu, 10 Oct 2002 12:28:37 -0800 Message-ID: Date: Thu, 10 Oct 2002 12:28:37 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Rajesh.Rao@jpmchase.com Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Rajesh.Rao@jpmchase.com Subject: Re: DROP DEVELOPER not working Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Whoa Stephane !!!! You might be onto something here. The developer confirmed that they do use Pro*C and cursors in the process. Will investigate. Thanks a ton Raj Stephane Faroult To: Multiple recipients of list ORACLE-L Subject: Re: DROP DEVELOPER not working Sent by: root@fatcity. com October 10, 2002 03:25 PM Please respond to ORACLE-L Rajesh.Rao@jpmchase.com wrote: > > We have a developer here, installing a third party application, who claims > one of his "delete campaign" process is hanging. I looked at the wait > events, saw nothing, and asked him to politely to go look at the code. > After much analysys, the developer now complains, that Oracle is not > executing a drop table command at the end of the process, and hanging > there. He claims he can drop the table from SQLPLUS. > > I asked him to rerun the process. I noticed no wait events for that session > in v$session_wait when he claims the process is hanging. I see no DROP > statements in the v$sqlarea. I did a 10046 trace, and the last statement in > the trace file is a select statement. I looked at the sql addresses from > v$session, linked it to v$sqlarea and the sql_text shows the same select > statement as is seen in the trace file. I see no exclusive locks on the > said table. I conclude that the application is not sending a DROP statement > to Oracle for execution. He claims that cannot be the case. They have done > the same installation in a test environment and it worked fine. The jury > seems to be taking sides. I scream SOS. What more should I be doing? And > Does an Oracle 10046 trace write into the trace file after the statement > has executed? > > Thanks > Raj > DROP DEVELOPER, what a nice idea ... Anyway, it reminds me of a similar problem I had ca 1990 in a Pro*C program. I was checking something in a table, cleanly closing my cursor, and trying to drop the table and it timed out each time. The reason was that although my cursor was closed, Pro*C was keeping it open in the hope that somewhat later I would reuse it and it would save a parse. The lock which was preventing me from dropping my table was not an exclusive lock, but a share lock on the dictionary - as long as a cursor references a table, you can't drop it. It was solved by adding the relevant bit of code (kind of pragma) to the Pro*C code. Does your saying 'I see no exclusive locks on the said table' implicitly means that you are seeing other locks? I think that there is one of those obscure init.ora parameters instructing Oracle to cache or not to cache closed cursors. This may be the difference between your test and prod environments. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult@oriole.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Rajesh.Rao@jpmchase.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).