oracle 锁 源头,请问一下，如何用语句查找oracle锁的源头。

1.

===================================================================

prompt

prompt Query 1. Waiting for TX Enqueue where mode is Exclusive

prompt =====================================

prompt

set linesize 100

set pagesize 66

col c1 for a15

col c1 heading "Program Name "

select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST

from gv$lock l,gv$session s

where l.type like 'TX' and l.REQUEST =6

and l.inst_id=s.inst_id and l.sid=s.sid

order by id1

/

2. Let's run the next query to find who is holding

===========================================

prompt

prompt

prompt Query 2. Holding for TX Enqueue where mode greater than 6

prompt =======================================

prompt

set linesize 100

set pagesize 66

col c1 for a15

col c1 heading "Program Name "

select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST

from gv$lock l,gv$session s

where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in

(select id1,id2 from gv$lock where type like 'TX' and REQUEST =6)

and l.inst_id=s.inst_id and l.sid=s.sid

order by id1

/

3. Let's find out the exact file#, block# and Record# where it is waiting

===============================================================

prompt

prompt

prompt Query 3. Object# ,File#, Block# and Slot# TX Enqueue in detail

prompt ========================================

prompt

set linesize 110

col c0 for 999

col c0 heading "INS"

col c1 for a15

col c1 heading "Program Name "

select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,

ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no

from gv$session

where (inst_id,sid) in (select inst_id,sid from gv$session_wait where p1='1415053318')

/

4.

prompt

prompt

prompt Query 4. Object Involve for TX Enqueue in detail

prompt ===============================

prompt

set linesize 100

set pagesize 100

col owner for a10

col object_name for a20

col object_type for a10

select owner,object_name,object_id,object_type

from dba_objects

where

object_id in (select ROW_WAIT_OBJ# from gv$session

where (inst_id, sid) in (select inst_id,sid from gv$session_wait where p1='1415053318'))

/

6. Let’s find the user activity that is "Holder" and "Waiter"

====================================================

set linesize 120

set pagesize 66

col c0 for 999

col c0 heading "INS"

col c1 for a9

col c1 heading "OS User"

col c2 for a9

col c2 heading "Oracle User"

col c3 for a15

col c3 heading "Program Name"

col b1 for a9

col b1 heading "Unix PID"

col b2 for 9999 justify left

col b2 heading "ORA SID"

col b3 for 999999 justify left

col b3 heading "SERIAL#"

col sql_text for a45

set space 1

break on b1 nodup on c0 nodup on c3 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 2

select a.inst_id c0,b.sid b2,c.spid b1, b.program c3, b.username c2,b.serial# b3, a.sql_text

from gv$sql a, gv$session b, gv$process c

where

a.address = b.sql_address

and b.paddr = c.addr

and a.hash_value = b.sql_hash_value

and a.inst_id=b.inst_id and a.inst_id=c.inst_id

and a.inst_id like '&inst_id' and b.sid like '&sid'

order by c.spid,a.hash_value

/

This query asks the Instance Number and Sid number, which you can get from step 1 and 2.

But remember , you can see the waiter activity, but you may not see the holder activity.

Reason is, the holder is sitting idle after doing the DML operation. So SQL for Holder

should not be seen under gv$sql.

metalink上的,我一直用这个,没问题