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';

========================================

我写的脚本，有点慢，将就着用吧
--kill session语句

alter system kill session'50,492';

--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;       
SELECT * FROM v$locked_object;   
SELECT * FROM all_objects;   
SELECT * FROM v$session_wait;


--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.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
--比上面那段多出sql_text和action
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
       l.os_user_name,s.machine, s.terminal,a.sql_text, a.action  
     FROM v$sqlarea a,v$session s, v$locked_object l
    WHERE l.session_id = s.sid
      AND s.prev_sql_addr = a.address 
ORDER BY sid, s.serial#;
      
--3.查出锁定表的sid, serial#,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;

==================================

key words: 锁表 解锁
查询sql:

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 )
 
 ORDER 
   
 BY 
   
 1 
 , 
 2

释放session Sql:

alter 
  system  
 kill 
  session  
 ' 
 sid, serial# 
 ' 
 

 
 alter 
  system  
 kill 
  session  
 ' 
 379, 21132 
 ' 
 
 
 alter 
  system  
 kill 
  session  
 ' 
 374, 6938 
 '