Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DROP DEVELOPER not working

Re: DROP DEVELOPER not working

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Thu, 10 Oct 2002 11:25:02 -0800
Message-ID: <F001.004E6118.20021010112502@fatcity.com>


Rajesh.Rao_at_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_at_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_at_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).
Received on Thu Oct 10 2002 - 14:25:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US