This discussion is archived
3 Replies Latest reply: Jan 31, 2013 12:03 AM by Jonathan Lewis RSS

sql of the blocker history

Ora_83 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Hi,

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

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

    Aman....
  • 3. Re: sql of the blocker history
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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

Incoming Links

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points