oracle資料庫查詢鎖表與解鎖語句

查詢鎖表語句

代碼如下

複製代碼

SELECT A.OWNER 方案名, A.OBJECT_NAME 表名, B.XIDUSN 復原段號, B.XIDSLOT 槽號, B.XIDSQN 序號, B.SESSION_ID 鎖表SESSION_ID, B.ORACLE_USERNAME 鎖表使用者名稱, decode(D.type, ‘XR’, ‘NULL’, ‘RS’, ‘SS(Row-S)’, ‘CF’, ‘SS(Row-S)’, ‘TM’, ‘TABLE LOCK’, ‘PW’, ‘TABLE LOCK’, ‘TO’, ‘TABLE LOCK’, ‘TS’, ‘TABLE LOCK’, ‘RT’, ‘ROW LOCK’, ‘TX’, ‘ROW LOCK’, ‘MR’, ‘S(Share)’, NULL) 鎖定方式, C.MACHINE 使用者組, C.TERMINAL 機器名, B.OS_USER_NAME 系統使用者名稱, B.PROCESS 系統進程id, DECODE(C.STATUS, ‘INACTIVE’, ‘不活動’, ‘ACTIVE’, ‘活動’) 活動情況, C.SERVER, C.SID, C.SERIAL#, C.PROGRAM 串連方式, C.LOGON_TIME FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d WHERE (A.OBJECT_ID = B.OBJECT_ID) AND (B.PROCESS = C.PROCESS) and C.sid = d.sid and B.LOCKED_MODE = D.LMODE ORDER BY 1, 2;

例2

查詢鎖定的表:

代碼如下	複製代碼
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username, 　　l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time 　　FROM v$locked_object l, all_objects o, v$session s 　　WHERE l.object_id = o.object_id 　　AND l.session_id = s.sid 　　ORDER BY sid, s.serial# ;

 

然後刪除掉系統鎖定的此記錄

例1

代碼如下	複製代碼
ALTER system KILL session 'SID,serial#';

如果有多個sid 和serial# 重複刪除每條記錄