oracle锁表问题解决

来源：互联网发布：免费附近聊天软件编辑：程序博客网时间：2024/05/29 19:21

--查询正在运行的job
SELECT SID,JOB FROM DBA_JOBS_RUNNING;
--找出对应的SID,SERIAL#
SELECT SID,SERIAL# FROM V$SESSION WHERE SID='256';

--检查是否有死锁
select username,lockwait,status,machine,program from v$session where

sid in
(select session_id from v$locked_object);

--查询死锁语句
select t.SQL_ID,t.SQL_TEXT from v$sql t where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

--查询死锁进程
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS,s.SQL_ID
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

--杀死session
--ALTER SYSTEM KILL SESSION 'sid,serial#';
alter system kill session '220,22733'; （其中sid=l.session_id）;

--找到锁定的SID和SERIAL#
select object_name,machine,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;

--找到系统呈面的SPID
select spid, osuser, s.program
from v$session s,v$process p

where s.paddr=p.addr and s.sid=267

以下是本人处理锁表常用方式
1. 查询当前用户被锁住的表和 sid
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
2. 查询当前用户被锁住表的 sid 和 serial
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
3. 根据 sid 和 serial 杀死这个session
alter system kill session '128,61979';

4. 如果第三步执行不能成功执行（根据 sid查出当前session 占用的系统进程）
select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid =128;

5. Linux 系统： kill -9 PID ， windows系统： ntsd -c q -p PID （PID 是第四步查询出的进程id）

0 0