Oracle中查锁

SELECT obj.OWNER || '.' || obj.OBJECT_NAME AS OBJ_NAME, -- 对象名称(已经被锁住)

obj.SUBOBJECT_NAME AS SUBOBJ_NAME, --子对象名称(已经被锁住)

obj.OBJECT_ID AS OBJ_ID, -- 对象ID

obj.OBJECT_TYPE AS OBJ_TYPE, -- 对象类型

lock_obj.SESSION_ID AS SESSION_ID, --会话SESSION_ID

lock_obj.ORACLE_USERNAME AS ORA_USERNAME, -- ORACLE系统用户名称

lock_obj.OS_USER_NAME AS OS_USERNAME, -- 操作系统用户名称

lock_obj.PROCESS AS PROCESS -- 进程编号

FROM (SELECT *

FROM all_objects

WHERE object_id IN (SELECT object_id FROM v$locked_object)) obj,

v$locked_object lock_obj

WHERE obj.object_id = lock_obj.object_id;

 

SELECT V$SESSION.sid,

v$session.SERIAL#,

v$process.spid,

rtrim(object_type) object_type,

rtrim(owner) || '.' || object_name object_name,

decode(lmode,

0, 'None',

1, 'Null',

2, 'Row-S',

3, 'Row-X',

4, 'Share',

5, 'S/Row-X',

6, 'Exclusive',

'Unknown') LockMode,

decode(request,

0, 'None',

1, 'Null',

2, 'Row-S',

3, 'Row-X',

4, 'Share',

5, 'S/Row-X',

6, 'Exclusive',

'Unknown') RequestMode,

ctime,

BLOCK b,

v$session.username,

MACHINE,

MODULE,

ACTION,

decode(A.TYPE,

'MR', 'Media Recovery',

'RT', 'Redo Thread',

'UN', 'User Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL User Lock',

'DX', 'Distributed Xaction',

'CF', 'Control File',

'IS', 'Instance State',

'FS', 'File Set',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalida-tion',

'LS', 'Log Start or Switch',

'RW', 'Row Wait',

'SQ', 'Sequence Number',

'TE', 'Extend Table',

'TT', 'Temp Table',

'Unknown') LockType

FROM (SELECT * FROM V$LOCK) A, all_objects, V$SESSION, v$process

WHERE A.sid > 6

AND object_name <> 'OBJ$'

AND A.id1 = all_objects.object_id

AND A.sid = v$session.sid

AND v$process.addr = v$session.paddr;