查看oracle死锁进程并结束死锁.docVIP

查看oracle死锁进程并结束死锁 博客分类： oracle数据库 oracle死锁进程解锁 查看锁表进程SQL语句1： select sess.sid, ?? sess.serial#, ?? lo.oracle_username, ?? lo.os_user_name, ?? ao.object_name, ?? lo.locked_mode ?? from v$locked_object lo, ?? dba_objects ao, ?? v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid; 查看锁表进程SQL语句2： select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 杀掉锁表进程： 如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK alter system kill session 738,1429; 用这个可以查：? select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode, ALTER? SYSTEM? KILL? SESSION? ||s.sid||,? ||s.serial#||;?? Command? from? v$locked_object? l,v$session? s,all_objects? o? where? l.session_id=s.sid? and? l.object_id=o.object_id? 可以查看哪台机器哪个用户锁了记录, 其中command是用来杀掉锁住记录的session ****************************************************************************************************************** SELECT??????? A.OBJECT_ID,??????? B.OBJECT_NAME,??????? A.SESSION_ID,??????? A.ORACLE_USERNAME,??????? A.OS_USER_NAME,??????? A.PROCESS,??????? A.LOCKED_MODE FROM V$LOCKED_OBJECT A, DBA_OBJECTS B WHERE A.OBJECT_ID = B.OBJECT_ID; SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME FROM V$LOCKED_OBJECT T1, V$SESSION T2 WHERE T1.SESSION_ID = T2.SID ORDER BY T2.LOGON_TIME; ALTER SYSTEM KILL SESSION sid, serial#; ******************************************************************************************************************** session 1: C:\sqlplus hxg/hxg SQL select * from scott.t; ???????? A B??????????????????? C ---------- -------------------- --------------------??????? 111 aa?????????????????? bb??????? 222 hello??????????????? world SQL update scott.t? set b=good where a=222; 已更新 1 行。 session 2: C:\sqlplus scott/tiger SQL select * from scott.t; ???????? A B??????????????????? C ---------- -------------------- --------------------??????? 111 aa?????????????????? bb??????? 222 hello??????????????? world SQL update t set b=asdfds