- /*
- *locked
- *querylockedobjectandanalysereason,killit
- *
- */
- select'altersystemkillsession'''||SID||','||SERIAL#||''';'
- from(selectdistincta.sid,
- a.Serial#,
- status,
- machine,226)"> LOCKWAIT,226)"> logon_time
- fromv$sessiona,v$locked_objectb
- where(a.status='ACTIVE'ora.status='INACTIVE')
- anda.sid=b.session_id
- andb.ORACLE_USERNAME='XYHISTEST'--加上用户名可过滤用户资源
- )
- /*2、批量执行第一步生成的语句
- altersystemkillsession'sid,serial#';
- altersystemkillsession'6976,33967';*/
- /*3、查询oracle用户名，机器名，锁表对象*/
- SELECTl.session_idsid,
- 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
- FROMv$locked_objectl,all_objectso,v$sessions
- WHEREl.object_id=o.object_id
- ANDl.session_id=s.sid
- ORDERBYsid,s.serial#;
- /*3.或者*/
- selects.SID,s.OSUSER,p.spidasOSPID,s.MACHINE,s.TERMINAL,s.PROGRAM
- fromv$sessions,v$processp
- wheres.sid=6--session_id
- ands.paddr=p.addr;
- /*4、查询是执行何sql语句导致锁表的*/
- selectb.sql_text
- sqlb
- wherea.sid=6anda.sql_ADDRESS=b.ADDRESS(+);
- /*5、查询是执行何sql语句导致锁表的*/
- s.user#,226)"> a.sql_text,226)"> a.action
- FROMv$sqlareaa,v$sessions,v$locked_objectl
- WHEREl.session_id=s.sid
- ANDs.prev_sql_addr=a.address
- 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
