查看oracle锁表信息并处理

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#'; 

key	描述
If-Modified-Since	缓存校验字段，值为资源最后一次的修改时间，即上次收到的Last-Modified值