oracle表锁问题
常用kill表锁命令
1、
--查看被锁的对象
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,
p.SPID
FROM v$locked_object l, all_objects o, v$session s,v$process p
WHERE l.object_id = o.object_id www.2cto.com
AND l.session_id = s.sid
AND s.paddr = p.addr
ORDER BY sid, s.serial#;
--方法1：alter system kill session 'sid,serial#'
alter system kill session '26,7013';
--方法2（方法1不灵时采用）：在操作系统命令行 orakill SID spid
orakill ORCL 4436
alter system kill session '26,7013';
--方法2（方法1不灵时采用）：在操作系统命令行 orakill SID spid
orakill ORCL 4436
2、查找被锁的表并杀掉
select 'alter system kill session '''||s.sid||','||s.serial#||''';' ,a.inst_id,
a.object_id,a.session_id,a.ORACLE_USERNAME,a.OS_USER_NAME,a.process,a.locked_mode,
b.object_name,s.sql_id,s.EVENT from gv$locked_object a ,dba_objects b,gv$session s where a.object_id=b.object_id
and a.session_id =s.sid(+) and a.inst_id=10 ;
3、当然也可以分开查
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, s.action, www.2cto.com
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn Desc;
kill锁首先要取得登录
Oracle用户的权限，再执行如下数据：
alter system kill session 'sid,SERIAL#'
4、这种方法简单易懂
SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME='TABLE1'-->OBJECT_ID=100
2.SELECT * FROM V$LOCKED_OBJECT WHERE OBJECT_ID=100 -->SESSION_ID=200
3.SELECT * FROM V$SESSION WHERE SID=200 -->SERIAL#=300
4.ALTER SYSTEM KILL SESSION '200,300'
SQL> desc v$locked_object;
Name Null Type
----------------------------------------- -------- ----------------------------
XIDUSN NUMBER
XIDSLOT NUMBER
XIDSQN NUMBER
OBJECT_ID NUMBER
SESSION_ID NUMBER
ORACLE_USERNAME VARCHAR2(30)
OS_USER_NAME VARCHAR2(30)
PROCESS VARCHAR2(24)
LOCKED_MODE NUMBER
SQL> desc v$session; www.2cto.com
Name Null Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER