Oracle锁表信息处理步骤

查看是否有锁表的sql
select ‘blocker(‘ || lb.sid || ‘:‘ || sb.username || ‘)-sql:‘ ||
       qb.sql_text blockers,‘waiter (‘ || lw.sid || ‘:‘ || sw.username || ‘)-sql:‘ ||
       qw.sql_text waiters
  from v$lock lb,v$lock lw,v$session sb,v$session sw,v$sql qb,v$sql qw
 where lb.sid = sb.sid
   and lw.sid = sw.sid
   and sb.prev_sql_addr = qb.address
   and sw.sql_address = qw.address
   and lb.id1 = lw.id1
   and sw.lockwait is not null
   and sb.lockwait is null
   and lb.block = 1;
查看被锁的表
select p.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_name
  from v$process p,v$session a,v$locked_object b,all_objects c
 where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id;
查看那个用户那个进程造成死锁，锁的级别
select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l,dba_objects b
查看连接的进程
SELECT sid,serial#,username,osuser FROM v$session;
查看是哪个session引起的
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;
杀掉进程
--sid是上一步查询出的sid和serid
            alter system kill session ‘sid,serial#‘; 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!