mysql 查询lock_查看数据库中的锁(LOCK),找出程序及SQL

SELECT /*+ rule */

s.username,

DECODE (l.TYPE,

'TM', 'TABLE LOCK',

'TX', 'ROW LOCK',

NULL

) lock_level, o.owner, o.object_name, o.object_type, s.SID,

s.serial#, s.terminal, s.machine, s.program, s.osuser

FROM v$session s, v$lock l, dba_objects o

WHERE l.SID = s.SID AND l.id1 = o.object_id(+) AND s.username IS NOT NULL;

SELECT /*+ rule */

s.username,

DECODE (l.TYPE,

'TM', 'TABLE LOCK',

'TX', 'ROW LOCK',

NULL

) lock_level, o.owner, o.object_name, o.object_type, s.SID,

s.serial#, s.terminal, s.machine, s.program, s.osuser, q.sql_text

FROM v$session s, v$lock l, dba_objects o, v$open_cursor c, v$sql q

WHERE l.SID = s.SID

AND l.id1 = o.object_id(+)

AND s.username IS NOT NULL

AND s.sql_address = c.address

AND s.sql_hash_value = c.hash_value

AND q.address = s.sql_address

AND q.hash_value = s.sql_hash_value;

alter system kill session 'SID,SERIAL#';