3 Replies Latest reply: Jan 31, 2013 2:03 AM by Jonathan Lewis RSS

    sql of the blocker history

    Ora_83
      Hi

      Oracle 10g.
      Lets say, I experienced a database lock 2 hours ago and I want to find which sql statement was blocking others.

      I know the sid of the blocker but I am unable to find the sql statement of the blocker.
      Also, when I check enterprise manager top activity only shows the waiter.

      I tried to run the queries from below link, but it only shows the information about the waiting session.

      http://oraclue.com/2009/10/15/blocking-locks-history/


      How can I find the sql statement of the particular sid from history ?
        • 1. Re: sql of the blocker history
          asahide
          Hi,

          If you can access ASH,
          SID -> ASH -> SQL_ID -> V$SQL..

          Regards,
          • 2. Re: sql of the blocker history
            Aman....
            Try session snapper .
            http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper

            Aman....
            • 3. Re: sql of the blocker history
              Jonathan Lewis
              Ora_83 wrote:

              Lets say, I experienced a database lock 2 hours ago and I want to find which sql statement was blocking others.

              I know the sid of the blocker but I am unable to find the sql statement of the blocker.
              Also, when I check enterprise manager top activity only shows the waiter.

              I tried to run the queries from below link, but it only shows the information about the waiting session.
              http://oraclue.com/2009/10/15/blocking-locks-history/

              How can I find the sql statement of the particular sid from history ?
              Generally speaking, there is no way. There is no direct link available between the lock and the statement. Any SQL-driven solution to the probably is depending on luck, or a little guesswork, and may give no answer or a false positive. For a simple demo to make the point see: http://jonathanlewis.wordpress.com/2009/04/19/locking-sql/

              Regards
              Jonathan Lewis