oracle鎖表與解表

--1.查出鎖定object的session的信息以及被鎖定的object名
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#;

-- 2、這個語句將查找到數據庫中所有的DML語句產生的鎖，還可以發現，任何DML語句其實產生了兩個鎖，一個是表鎖，一個是行鎖。SELECT /*+ rule */
 s.username,
 decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
 o.owner,
 o.object_name,
 o.object_type,
 s.sid,
 s.serial#,
 s.terminal,
 s.Machine,
 s.program,
 s.osuser
  FROM v$session s, v$lock l, dba_objects o
 WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT NULL

--- 3、通過以下sql來查找sid ,serial# 和相關信息

SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.Oracle_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MacHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE A.OBJECT_ID = B.OBJECT_ID
AND B.PROCESS = C.PROCESS  and C.STATUS='ACTIVE'
ORDER BY 1,2

---4、解鎖，殺掉session進程

alter system kill session 'sid,serial#'