Oracle查鎖並解鎖

1).

--查鎖
select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as "已鎖物件名稱",  --物件名稱(已經被鎖住)     
       LOCK_INFO.SUBOBJ_NAME as "已鎖子物件名稱",     -- 子物件名稱(已經被鎖住)  
       SESS_INFO.MACHINE as "機器名稱",     -- 機器名稱                
       LOCK_INFO.SESSION_ID as "會話ID",    -- 會話SESSION_ID          
       SESS_INFO.SERIAL# as "會話SERIAL#",     -- 會話SERIAL#             
       SESS_INFO.SPID as "OS系統的SPID",     -- OS系統的SPID        
       (SELECT INSTANCE_NAME FROM V$INSTANCE) "實例名SID", --實例名SID         
       LOCK_INFO.ORA_USERNAME as "ORACLE用戶",     -- ORACLE系統用戶名稱      
       LOCK_INFO.OS_USERNAME as "OS用戶",     -- 作業系統用戶名稱        
       LOCK_INFO.PROCESS as "進程編號",     -- 進程編號                
       LOCK_INFO.OBJ_ID as "對象ID",      -- 對象ID                  
       LOCK_INFO.OBJ_TYPE as "對象類型",     -- 對象類型                
       SESS_INFO.LOGON_TIME as "登錄時間",     -- 登錄時間                
       SESS_INFO.PROGRAM as "程式名稱",     -- 程式名稱                
       SESS_INFO.STATUS as "會話狀態",      -- 會話狀態                
       SESS_INFO.LOCKWAIT as "等待鎖",      -- 等待鎖                  
       SESS_INFO.ACTION as "動作",      -- 動作                    
       SESS_INFO.CLIENT_INFO as "客戶資訊"     -- 客戶資訊                
  from (select obj.OWNER                as OWNER,
               obj.OBJECT_NAME          as OBJ_NAME,
               obj.SUBOBJECT_NAME       as SUBOBJ_NAME,
               obj.OBJECT_ID            as OBJ_ID,
               obj.OBJECT_TYPE          as OBJ_TYPE,
               lock_obj.SESSION_ID      as SESSION_ID,
               lock_obj.ORACLE_USERNAME as ORA_USERNAME,
               lock_obj.OS_USER_NAME    as OS_USERNAME,
               lock_obj.PROCESS         as PROCESS
          from (select *
                  from all_objects
                 where object_id in (select object_id from v$locked_object)) obj,
               v$locked_object lock_obj
         where obj.object_id = lock_obj.object_id) LOCK_INFO,
       (select SID,
               SERIAL#,
               LOCKWAIT,
               STATUS,
               (select spid from v$process where addr = a.paddr) spid,
               PROGRAM,
               ACTION,
               CLIENT_INFO,
               LOGON_TIME,
               MACHINE
          from v$session a) SESS_INFO
 where LOCK_INFO.SESSION_ID = SESS_INFO.SID
order by  LOCK_INFO.SESSION_ID;

2).
select sql_text
  from v$sqltext
 where address in (select sql_address from v$session where sid = &sid)
 order by piece;
3).
ALTER SYSTEM KILL SESSION '會話ID,會話SERIAL#'; --解鎖

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/wizardlun/archive/2010/03/30/5432058.aspx