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

Search Discussions

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

Related Discussions

People

Translate

site design / logo © 2022 Grokbase