Oracle中查询正锁表的用户及释放被锁的表的方法

查询oracle锁定的表及杀掉锁表的进程,注意查看的时候要用sys/sys 管理员的身份登录

 

1、首先查看锁定的表太进程：

 

SELECT substr(v$lock.sid,1,4) "SID",

      serial#,

      V$SESSION.sid,

       substr(username,1,12) "UserName",

       substr(object_name,1,25) "ObjectName",

       v$lock.type "LockType",  www.2cto.com  

       decode(rtrim(substr(lmode,1,4)),

 

       '2','Row-S (SS)','3','Row-X (SX)',

       '4','Share',     '5','S/Row-X (SSX)',

       '6','Exclusive', 'Other' ) "LockMode",

       substr(v$session.program,1,25) "ProgramName"
 

FROM V$LOCK,SYS.DBA_OBJECTS,V$SESSION

WHERE (OBJECT_ID = v$lock.id1

      AND v$lock.sid = v$session.sid

      AND username IS NOT NULL

      AND username NOT IN ('SYS','SYSTEM')

      AND SERIAL# != 1);

 

2、杀掉锁表的进程：  www.2cto.com  

 

alter   system     kill   session   'sid,serial#';