oracle 锁状态,oracle运维--07.查看锁资源情况

1.查看锁表情况；

SELECT /*+ RULE */

Ls.Osuser Os_User_Name,

Ls.Username User_Name,

Decode(Ls.TYPE,

'RW',

'Row wait enqueue lock',

'TM',

'DML enqueue lock',

'TX',

'Transaction enqueue lock',

'UL',

'User supplied lock') Lock_Type,

o.Object_Name OBJECT,

Decode(Ls.Lmode,

1,

NULL,

2,

'Row Share',

3,

'Row Exclusive',

4,

'Share',

5,

'Share Row Exclusive',

6,

'Exclusive',

NULL) Lock_Mode,

o.Owner,

Ls.Sid,

Ls.Serial# Serial_Num,

Ls.Id1,

Ls.Id2

FROM Sys.Dba_Objects o,

(SELECT s.Osuser,

s.Username,

l.TYPE,

l.Lmode,

s.Sid,

s.Serial#,

l.Id1,

l.Id2

FROM V$session s, V$lock l

WHERE s.Sid = l.Sid) Ls

WHERE o.Object_Id = Ls.Id1

AND o.Owner <> 'SYS'

ORDER BY o.Owner, o.Object_Name;

2.查看锁表、主机、SID/SERIAL

select l.session_id sid,

s.serial#,

l.locked_mode,

l.oracle_username,

l.os_user_name,

s.machine,

s.terminal,

o.object_name,

s.logon_time,

'alter system kill session '''||l.session_id||','||s.serial#||''';' kill会话

from v$locked_object l, all_objects o, v$session s

where l.object_id = o.object_id

and l.session_id = s.sid

order by sid, s.serial#;

3.查看导至锁表的sql语句(汇总查询)；

select l.session_id sid,

s.serial#,

l.locked_mode,

l.oracle_username,

s.user#,

l.os_user_name,

s.machine,

s.terminal,

a.sql_text,

a.SQL_FULLTEXT,

o.object_name

from v$sqlarea a, v$session s, v$locked_object l,all_objects o

where l.object_id = o.object_id

and l.session_id = s.sid

and s.prev_sql_addr = a.address

order by sid, s.serial#;

4.sid查看sql语句；

select b.sql_text

from v$session a, v$sql b

where a.sid = SID号 --session_id

and a.SQL_ADDRESS = b.ADDRESS(+);

5.删除锁表的会话；

----3028:SID,15898:SERIAL#

alter system kill session '3028,15898';