[Oracle-l] DROP DEVELOPER not working

DENNIS WILLIAMS

at Oct 9, 2002 at 11:24 pm

Raj - What about just a normal trace file? It should show a list of the
commands sent, including the drop table. Maybe killing the session will
cause the O.S. to flush the trace write buffer. Just a thought.

Dennis Williams
DBA

Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Wednesday, October 09, 2002 4:28 PM
To: Multiple recipients of list ORACLE-L

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

--
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.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).

reply | permalink

Robert Eskridge

at Oct 9, 2002 at 11:24 pm

⇧

Is the application coming from a client? If so, sniff the packets and
look for his DROP. That should be pretty conclusive. You can see the
packets in a SQL*Net trace by setting TRACE_LEVEL_CLIENT=16 in
sqlnet.ora.

R> We have a developer here, installing a third party application, who claims
R> one of his "delete campaign" process is hanging. I looked at the wait
R> events, saw nothing, and asked him to politely to go look at the code.
R> After much analysys, the developer now complains, that Oracle is not
R> executing a drop table command at the end of the process, and hanging
R> there. He claims he can drop the table from SQLPLUS.

R> I asked him to rerun the process. I noticed no wait events for that session
R> in v$session_wait when he claims the process is hanging. I see no DROP
R> statements in the v$sqlarea. I did a 10046 trace, and the last statement in
R> the trace file is a select statement. I looked at the sql addresses from
R> v$session, linked it to v$sqlarea and the sql_text shows the same select
R> statement as is seen in the trace file. I see no exclusive locks on the
R> said table. I conclude that the application is not sending a DROP statement
R> to Oracle for execution. He claims that cannot be the case. They have done
R> the same installation in a test environment and it worked fine. The jury
R> seems to be taking sides. I scream SOS. What more should I be doing? And

R> Does an Oracle 10046 trace write into the trace file after the statement
R> has executed?

R> Thanks
R> Raj

R> --
R> Please see the official ORACLE-L FAQ: http://www.orafaq.com

-rje

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Eskridge
INET: bryny_at_dfweahs.net

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

reply | permalink

Jared.Still_at_radisys.com

at Oct 10, 2002 at 12:03 am

⇧

"It worked fine in development!"

I can't believe anyone would still say that.

Has your duhveloper traced the code in the current environment,
to ensure that the offending piece of code is actually being executed?

Jared

Rajesh.Rao_at_jpmchase.com
Sent by: root_at_fatcity.com
10/09/2002 02:28 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L
cc:
Subject: DROP DEVELOPER not working

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

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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Jared.Still_at_radisys.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).

reply | permalink

Charlie_Mengler_at_HomeDepot.com

at Oct 10, 2002 at 1:13 am

⇧

Privs obtained via roles (such as DROP TABLE) are NOT transferred (by
default) when inside PL/SQL procedures.

Rajesh.Rao_at_jpmcha
se.com To: Multiple recipients of list ORACLE-L
Sent by: cc:
root_at_fatcity.com Subject: DROP DEVELOPER not working

10/09/2002 02:28
PM
Please respond to
ORACLE-L

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

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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Charlie_Mengler_at_HomeDepot.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).

reply | permalink

Deshpande, Kirti

at Oct 10, 2002 at 2:58 am

⇧

Revoke the drop/delete privilege from role/userid, and ask them to run the
process. That would confirm if the code ever encounters the drop/delete
instruction.
The process could very well be data dependent...

Kirti

-----Original Message-----
Sent: Wednesday, October 09, 2002 7:04 PM
To: Multiple recipients of list ORACLE-L

"It worked fine in development!"

I can't believe anyone would still say that.

Has your duhveloper traced the code in the current environment,
to ensure that the offending piece of code is actually being executed?

Jared

Rajesh.Rao_at_jpmchase.com
Sent by: root_at_fatcity.com
10/09/2002 02:28 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L
cc:
Subject: DROP DEVELOPER not working

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

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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Jared.Still_at_radisys.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: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.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).

reply | permalink

Rajesh.Rao_at_jpmchase.com

at Oct 10, 2002 at 5:38 am

⇧

Thats a nice idea. The problem here is that the owner of the table is the
one executing the drop. And its not via SQLPlus. So, the
use of product_profile is also ruled out. The SQLNet tracing suggested by
Robert also seems a good idea, worth trying out. They have been pointing me
to their log file which shows the DROP statement, which is the last line in
the log file. Maybe its writing to the logs first.

Now, when does a 10046 trace write to the tracefile? Does it wait for the
statement to finish executing, before it writes to the files.
I dont think so. I remember the trace file showing me statements with bind
variables even as a plsql block was running. Self doubt creeping in. In
need of an expert opinion.

Thanks
Raj

"Deshpande, Kirti"

rizon.com> cc:
Sent by: Subject: RE: DROP DEVELOPER not working
root_at_fatcity.com

October 09, 2002
10:58 PM
Please respond to
ORACLE-L

Revoke the drop/delete privilege from role/userid, and ask them to run the
process. That would confirm if the code ever encounters the drop/delete
instruction.
The process could very well be data dependent...

Kirti

-----Original Message-----
Sent: Wednesday, October 09, 2002 7:04 PM
To: Multiple recipients of list ORACLE-L

"It worked fine in development!"

I can't believe anyone would still say that.

Has your duhveloper traced the code in the current environment,
to ensure that the offending piece of code is actually being executed?

Jared

Rajesh.Rao_at_jpmchase.com
Sent by: root_at_fatcity.com
10/09/2002 02:28 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L
cc:
Subject: DROP DEVELOPER not working

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

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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Jared.Still_at_radisys.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: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.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).

reply | permalink

Jamadagni, Rajendra

at Oct 10, 2002 at 11:38 am

⇧

(taking a cue from RMAN syntax)

drop developer from third_floor position upside_down
immediate
/

works for me all the time ... (sorry couldn't resist ... I have been a
developer too for 8+ years)

BTW 10046 will write the sql statement to trace file as soon as it is parsed
... it will have appropriate entry for execution as well ...

Also, see if developer is doing all this inside a script ... take a closer
look for a missing / or a semicolon ...

Sometimes the best solution is an extra pair of eyes to see your code pick
the person who hates you most, chances are he/she will find the most bugs in
the least anount of time. It's human nature ...

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.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).

text/plain attachment: ESPN_Disclaimer.txt

reply | permalink

Dgoulet_at_vicr.com

at Oct 10, 2002 at 3:38 pm

⇧

Raj,

I'll take OTS's point of view on the subject, namely if the duhveloper can
drop the table via SQL*Plus then he needs to call the vendor of the third party
application. The database responds to a command the same way no matter where
the command is issued from if it is complete and executed. More than likely
there's either a bug in the third party code or else in his configuration
thereof.

More than likely, and I've seen this before (actually did it myself), the
application is doing something like a 'select count(*) from;' and as
long as count(*) > 0 it will wait, Well when the application leaves data laying
around without cleaning up after itself guess what happens. DUH!!!

Dick Goulet

PS: I liked the "drop duhveloper off of third floor balcony" idea. But I still
prefer the 12 gauge shotgun method. It's faster & leaves a more noticeable
mark!! *-)

____________________Reply Separator____________________
Author: Rajesh.Rao_at_jpmchase.com
Date: 10/9/2002 1:28 PM

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

--
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: dgoulet_at_vicr.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).

reply | permalink

Fink, Dan

at Oct 10, 2002 at 4:08 pm

⇧

I hear that they are enhancing that command in 10i. You know have the option
of naming the employment position:
drop manager
drop user
drop cow-worker
etc...

I recall that in Unix, the trace file is written right away, but NT waits
for the process to complete. *Could be wrong on this one...only 1 cup of
coffee this morning

-----Original Message-----
Sent: Thursday, October 10, 2002 5:39 AM
To: Multiple recipients of list ORACLE-L

(taking a cue from RMAN syntax)

drop developer from third_floor position upside_down
immediate
/

works for me all the time ... (sorry couldn't resist ... I have been a
developer too for 8+ years)

BTW 10046 will write the sql statement to trace file as soon as it is parsed
... it will have appropriate entry for execution as well ...

Also, see if developer is doing all this inside a script ... take a closer
look for a missing / or a semicolon ...

Sometimes the best solution is an extra pair of eyes to see your code pick
the person who hates you most, chances are he/she will find the most bugs in
the least anount of time. It's human nature ...

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: Dan.Fink_at_mdx.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).

reply | permalink

Jesse, Rich

at Oct 10, 2002 at 4:39 pm

⇧

"DROP COW-WORKER" only works here in the Dairy State...;)

Rich

Rich Jesse System/Database Administrator
Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Thursday, October 10, 2002 11:09 AM
To: Multiple recipients of list ORACLE-L

I hear that they are enhancing that command in 10i. You know have the option
of naming the employment position:
drop manager
drop user
drop cow-worker
etc...

I recall that in Unix, the trace file is written right away, but NT waits
for the process to complete. *Could be wrong on this one...only 1 cup of
coffee this morning
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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).

reply | permalink

Stephane Faroult

at Oct 10, 2002 at 7:25 pm

⇧

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

reply | permalink

Rajesh.Rao_at_jpmchase.com

at Oct 10, 2002 at 8:28 pm

⇧

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

reply | permalink

group	oracle-l @
categories	oracle
posted	Oct 9, '02 at 9:28p
active	Oct 10, '02 at 8:28p
posts	13
users	11
website	oracle.com

responses	expanded	Hotkey: s
font	variable	Hotkey: f
user style	avatars	Hotkey: a

[Oracle-l] DROP DEVELOPER not working

Search Discussions

Related Discussions

11 users in discussion