Re: List of SQL involved in a transaction

  • From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 04 Feb 2009 06:44:14 -0700

If the sql issued in these sessions/transactions is part of a pl/sql stored procedure, you *might* be able to use v$sql.program_id to locate the stored procedure. The program_id is the object_id for the pl/sql procedure that *first* parsed the statement. This may not be the case in your situation, but it is worth taking a look.

If you are using statspack and have a snapshot from the time where there were problems, you should look for the sql_id in stats$sql_summary and use the value of program_id.

This method is far from exact (if the application is not using pl/sql stored procs and/or the sql was not first parsed by the current procedure the info can't be used), but it may direct you to the proper code.

Daniel Fink

Daniel Fink - Oracle Performance, Diagnosis, Data Recovery and Training

Oracle Blog

Lost Data?

Vishal Gupta wrote:
Thanks for all the responses.

Vishal Gupta

On 3 Feb 2009, at 17:42, "Cary Millsap" <cary.millsap@xxxxxxxxxxxx <mailto:cary.millsap@xxxxxxxxxxxx>> wrote:

Yes, there may be lots of transactions in a session, but they're delimited by XCTEND lines. If you're using autonomous transactions, it might be a little tricky, but if you're not, then the job should be very straightforward.

Cary Millsap

On Tue, Feb 3, 2009 at 11:05 AM, Vishal Gupta <vishal@xxxxxxxxxxxxxxx <mailto:vishal@xxxxxxxxxxxxxxx>> wrote:

    yes trace is one of the way. But then there could be lot of
    transactions in a session. I was more interested in finding
    information from v$ or x$ views.

    Say, you have session blocking another session. And you can find
    out what objects are locked by blocking session and identify the
    session as well. But one wanted to find out all the SQL
    statements already executed by this blocking session in current
    transaction, how do you generate this list. V$TRANSACTION gives
    information about current SQL_ID and pre_sql_id. But not all of them.

    Vishal Gupta


    From: oracle-l-bounce@xxxxxxxxxxxxx
    <mailto:oracle-l-bounce@xxxxxxxxxxxxx> on behalf of Cary Millsap
    Sent: Tue 03/02/2009 16:07
    To: Vishal Gupta
    Cc: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
    Subject: Re: List of SQL involved in a transaction



    On Tue, Feb 3, 2009 at 9:59 AM, Vishal Gupta
    <vishal@xxxxxxxxxxxxxxx <mailto:vishal@xxxxxxxxxxxxxxx>> wrote:


           Does anyone know how to find list of all the SQLs involved
    in a transaction?

           Vishal Gupta


Other related posts: