Oracle 检查锁的脚本 _MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 1572 | 回复: 0

主题： Oracle 检查锁的脚本

ganxinge

注册用户

等级：少校
经验：893
发帖：66
精华：0
注册：2013-2-22
状态：离线
发送短消息息给ganxinge

加好友发送短消息息给ganxinge

发消息

发表于：

2015-8-26 10:25:15 | [全部帖] [楼主帖]

楼主

COL OBJECT_NAME format a20;
COL USER_NAME format a20;
SELECT /*+ rule */
LPAD ('--', DECODE (b.BLOCK, 1, 0, 4)) s.username user_name,
b.TYPE, o.owner '.' o.object_name object_name, s.SID,
s.serial#, DECODE (b.request, 0, 'BLOCKED', 'WAITING') status
FROM dba_objects o, v$session s, v$lock v, v$lock b
WHERE v.id1 = o.object_id
AND v.SID = s.SID
AND v.SID = b.SID
AND (b.BLOCK = 1 OR b.request > 0)
AND v.TYPE = 'TM'
ORDER BY b.id2, v.id1, user_name DESC;

北京联动北方科技有限公司 SET linesize 200
COLUMN sid format 999;
COLUMN b format 9;
COLUMN spid format 999999;
COLUMN object_type format a5
COLUMNobject_name format a30;
COLUMN lock_type format a10;
COLUMN ctime format 99999
COLUMN username format a15
COLUMN machine format a20;
北京联动北方科技有限公司 COLUMN MODULE format a20;
COLUMN action format a20;

SELECT v$session.SID, v$session.serial#, v$process.spid,
RTRIM (object_type) object_type,
RTRIM (owner) '.' object_nameobject_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
ANDobject_name<>'OBJ$'
AND a.id1 = all_objects.object_id
北京联动北方科技有限公司 AND a.SID = v$session.SID
AND v$process.addr = v$session.paddr;