Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

find the sql cause lock,I use the sql (uncommit or rollback sql statement)

xiongye_2
Occasional Advisor

find the sql cause lock,I use the sql (uncommit or rollback sql statement)

Hi: I want to find the sql cause lock,I use the sql

SELECT object_name,SESSION_ID,SERIAL#,machine,
osuser,S.PROCESS,e.sql_text
FROM V$LOCKED_OBJECT p, V$SESSION S , dba_objects B, v$sql e, v$lock l
WHERE p.SESSION_ID=S.SID and b.OBJECT_ID=p.OBJECT_ID and
l.addr=e.ADDRESS
and l.sid=s.sid;
but $lock 's addr and v$sql 's address is not equal?
or
How can I find the sql cause lock ?

Tks??????

xysco
2 REPLIES
Jeanine Kone
Trusted Contributor

Re: find the sql cause lock,I use the sql (uncommit or rollback sql statement)

You can join v$session to v$sql as shown below.
This may eliminate the need to join v$lock with v$sql.

select s.username, s.sid, t.sql_text "Last SQL"
from v$session s, v$sql t
where s.sql_address =t.address and s.sql_hash_value =t.hash_value

Hope this helps, Jeanine
Scott Benitez
Occasional Advisor

Re: find the sql cause lock,I use the sql (uncommit or rollback sql statement)

Change l.addr to s.sql_address

SELECT object_name, SESSION_ID, SERIAL#, machine, osuser, S.PROCESS, e.sql_text
FROM V$LOCKED_OBJECT p, V$SESSION S , DBA_OBJECTS B, v$sql e, v$lock l
WHERE p.SESSION_ID=S.SID AND b.OBJECT_ID=p.OBJECT_ID AND
s.sql_address=e.ADDRESS
AND l.sid=s.sid;

I also have a sql script I modified to givel sql output. The formatting is questionable (but it serves my needs). It uses dbms_output so keep in mind the upper bound of the buffer. It gives all the session info, spid, login time, status, ... It faster than trying to join all the tables together.

Scott

Die Laughing