oracle 查看锁表情况并处理锁表

前端之家收集整理的这篇文章主要介绍了oracle 查看锁表情况并处理锁表前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
  1. /*
  2. *locked
  3. *querylockedobjectandanalysereason,killit
  4. *
  5. */
  6. select'altersystemkillsession'''||SID||','||SERIAL#||''';'
  7. from(selectdistincta.sid,
  8. a.Serial#,
  9. status,
  10. machine,226)"> LOCKWAIT,226)"> logon_time
  11. fromv$sessiona,v$locked_objectb
  12. where(a.status='ACTIVE'ora.status='INACTIVE')
  13. anda.sid=b.session_id
  14. andb.ORACLE_USERNAME='XYHISTEST'--加上用户名可过滤用户资源
  15. )
  16. /*2、批量执行第一步生成的语句
  17. altersystemkillsession'sid,serial#';
  18. altersystemkillsession'6976,33967';*/
  19. /*3、查询oracle用户名,机器名,锁表对象*/
  20. SELECTl.session_idsid,
  21. s.serial#,226)"> l.locked_mode,226)"> l.oracle_username,226)"> l.os_user_name,226)"> s.machine,226)"> s.terminal,226)"> o.object_name,226)"> s.logon_time
  22. FROMv$locked_objectl,all_objectso,v$sessions
  23. WHEREl.object_id=o.object_id
  24. ANDl.session_id=s.sid
  25. ORDERBYsid,s.serial#;
  26. /*3.或者*/
  27. selects.SID,s.OSUSER,p.spidasOSPID,s.MACHINE,s.TERMINAL,s.PROGRAM
  28. fromv$sessions,v$processp
  29. wheres.sid=6--session_id
  30. ands.paddr=p.addr;
  31. /*4、查询是执行何sql语句导致锁表的*/
  32. selectb.sql_text
  33. sqlb
  34. wherea.sid=6anda.sql_ADDRESS=b.ADDRESS(+);
  35. /*5、查询是执行何sql语句导致锁表的*/
  36. s.user#,226)"> a.sql_text,226)"> a.action
  37. FROMv$sqlareaa,v$sessions,v$locked_objectl
  38. WHEREl.session_id=s.sid
  39. ANDs.prev_sql_addr=a.address
  40. sqlarea; SELECT * FROM v$session; SELECT * FROM v$process ; SELECT * FROM v$locked_object; SELECT * FROM all_objects; SELECT * FROM v$session_wait; --查看被锁的表 select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id; --查看那个用户那个进程照成死锁 select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time; --查看连接的进程 SELECT sid,serial#,username,osuser FROM v$session; --3.查出锁定表的sid,os_user_name,machine_name,terminal,锁的type,mode SELECT s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.process,s.machine,s.terminal,s.logon_time,l.type FROM v$session s,v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid; 这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现, 任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。 --杀掉进程 sid,serial# alter system kill session'210,11562';

    原文链接:https://www.f2er.com/oracle/208627.html

猜你在找的Oracle相关文章