Ora_83 wrote: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/
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.
How can I find the sql statement of the particular sid from history ?