Oracle-L: Re: DROP DEVELOPER not working

From: <Rajesh.Rao_at_jpmchase.com>
Date: Thu, 10 Oct 2002 12:28:37 -0800
Message-ID: <F001.004E62C1.20021010122837@fatcity.com>

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 <ORACLE-L_at_fatcity.com>        
                    <sfaroult_at_ori        cc:                                                                        
                    ole.com>             Subject:     Re: DROP DEVELOPER not working                                
                    Sent by:                                                                                        
                    root_at_fatcity.                                                                                   
                    com                                                                                             
                                                                                                                    
                                                                                                                    
                    October 10,                                                                                     
                    2002 03:25 PM                                                                                   
                    Please                                                                                          
                    respond to                                                                                      
                    ORACLE-L

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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_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_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 - 15:28:37 CDT