Re: How to find the exact SQL locking others?
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Jul 2012 14:31:58 -0400
summary: figure out which table your blocked sessions sql is blocking on.
See below for easy ways to parse that
go to v$open_cursor for the blocking session (from v$blocking_session) and
find queries that have that table name. Parse out for selects that don't
use select for update). should be a relatively small list.
detailed version:
use v$block_Session to get the blocking session.
parse the waiting sql of the session that is being blocked to see what
table it is being blocked on. you can use both v$sql_plan and v$sqlarea
updates,deletes are easy
select object_name
from v$sql_plan
where sql_id = <sql_id>
where id = 1
and operation in ('UPDATE','DELETE')
inserts require you to go to v$sqlarea. best to use regular expressions.
can be a bit tricky if your inserts can add hints.
then there are all the other possibilities such as DDL. but you may not
need to parse all that.
so now you know which table is being blocked.
v$open_cursor.sql_id for all open queries in the blocking session
get a list of sql that has your table_name in it. you can more specifically
filter out SELECT statements, etc...
you may see more than 1, but the list should be alot smaller.
On Mon, Jul 2, 2012 at 3:45 AM, Leyi Kamus Zhang <kamusis@xxxxxxxxx> wrote:
> Hi Lists
> Maybe the question is not so easy as it looked from subject.
>
> Session 1:
> SQL> update t set n=2 where n=1;
> <<==== no commit here
> SQL> select sysdate from dual;
> SQL> select table_name from tabs;
> <<==== run any SQL that you want, to age out the SQL_ID and PREV_SQL_ID in
> v$session
>
> Session 2:
> SQL> update t set n=3 where n=1;
> <<===== will hang in wait for "TX-row lock contention"
>
> My question is: How to find the exact SQL "update t set n=2 where n=1" was
> issued in session 1?
>
> I tried:
> 1. from v$active_session_history, the SQL is not captured by sampling
> 2. from v$open_cursor, no luck
> 3. oradebug dump processstate, no SQL text
> 4. oradebug hanganalyze, no SQL text
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
