数据库版本是oracle 11.2.0.4.0,架构是单实例dg主从,物理standby,执行语句和查看日志的都是在主库
用的查询死锁的语句:
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.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 Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
得到的结果:
PU_APPS PU_APPS 1654 1712 13879 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 1199 54359 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 1771 116 45785 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 858 54359 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 1712 1314 2975 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 1884 54359 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
表明有死锁,但是在数据库的alert日志中并没有发现 ORA-00060的日志,请大神进来指点分析一下
用的查询死锁的语句:
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.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 Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
得到的结果:
PU_APPS PU_APPS 1654 1712 13879 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 1199 54359 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 1771 116 45785 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 858 54359 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 1712 1314 2975 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 1884 54359 0000000627E2BEA0 3274778297 JDBC Thin Client JDBC Thin Client
表明有死锁,但是在数据库的alert日志中并没有发现 ORA-00060的日志,请大神进来指点分析一下
解决方案 »
- oracle11G数据库系统重装,忘记备份啦,只有放在D盘下的datafile文件,如何恢复啊
- 100分跪救删除表空间的问题
- 监听进程不能解析在连接描述符中给出的 SERVICE_NAME,如何解决?
- 请问能不能用Select的方法调用Oracle储存过程的返回结果集函数
- xmltype的长度最多是多少 在线等!!!!
- ORACLE的存储过程里怎么连接和访问SQL SERVER数据库(100)
- 如何得知是哪个session id更新记录的...
- 备份时,这几个%号代表什么???
- plsql中文乱码问题...
- oracle 如何用sql实现split功能?
- 有大佬帮忙看一下吗
- select 查询速度快,而insert into 变慢的问题。
死锁的话上面说了,3秒内就会有会话报错,而且实例的alert日志里会写入死锁ORA-60错误。
锁等待,也就是你说的阻塞是其中一个请求锁资源的会话等待另外一个持锁的会话,而死锁是两个会话之间相互等待,因为死锁的检测机制,在这种情况下,3秒之内,必定会有其中一个会话爆出ORA-60,并写入alert日志,时间太快,你想查也来不及。