ORACLE 如何查询被锁定表及如何解锁释放session

ORACLE数据库操作某一个表时发现一直出于假死状态，可能是该表被某一用户锁定，接下来为你详细介绍下查询被锁定表及如何解锁，希望可以帮助到你

锁表查询

--锁表查询SQL
SELECT object_name, machine, s.sid, s.serial# 
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id 
AND l.session_id = s.sid;

找到被锁定的表，解锁

--释放SESSION SQL: 
--alter system kill session 'sid, serial#'; 
--使用第一步执行出来的sid和serial
ALTER system kill session '23, 1647'; 

样例

假设系统项目目前在运行如下SQL

declare
  test number;
begin
  while true LOOP
    select 1 + 1 into test from dual;
  end LOOP;
end;

查看全部session中SQL

--查看全部session中SQL
select substr(s.username, 1, 18) username,
       s.sid,
       s.serial#,
       s.machine,
       y.sql_text
  from v$session     s,
       v$process     p,
       v$transaction t,
       v$rollstat    r,
       v$rollname    n,
       v$sql         y,
       v$process     z
 where s.paddr = p.addr
   and s.taddr = t.addr(+)
   and t.xidusn = r.usn(+)
   and r.usn = n.usn(+)
   and s.username is not null
   and s.sql_address = y.address
   and s.paddr = z.addr
--根据操作系统进程号查看某个session
--and z.spid = &pid
--查看某个时，检索条件中指定s.sid值即可
--and s.sid=&sid
 order by s.sid, s.serial#, s.username, s.status

查询Oracle正在执行的sql语句及执行该语句的用户

--查询Oracle正在执行的sql语句及执行该语句的用户
SELECT b.sid      oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid       操作系统ID,
       paddr,
       sql_text   正在执行的SQL,
       b.machine  计算机名
  FROM v$process a, v$session b, v$sqlarea c
 WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value;

获得需要kill session的信息(使用V$SESSION和GV$SESSION视图)

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.paddr,
       s.STATUS
  FROM gv$session s
  JOIN gv$process p
    ON p.addr = s.paddr
   AND p.inst_id = s.inst_id
 WHERE s.type != 'BACKGROUND'
 AND S.USERNAME IS NOT NULL;

当前库的Sql总体运行情况

select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser
 from (select c.USERNAME,
              a.event,
              to_char(a.cnt) as seconds,
              a.sql_id,
              dbms_lob.substr(b.sql_fulltext, 100, 1) sqltext
         from (select rownum rn, t.*
                 from (select decode(s.session_state,
                                     'WAITING',
                                     s.event,
                                     'Cpu + Wait For Cpu') Event,
                              s.sql_id,
                              s.user_id,
                              count(*) CNT
                         from v$active_session_history s
                        where sample_time > sysdate - 30 / 1440
                        group by s.user_id,
                                 decode(s.session_state,
                                        'WAITING',
                                        s.event,
                                        'Cpu + Wait For Cpu'),
                                 s.sql_id
                        order by CNT desc) t
                where rownum < 20) a,
              v$sqlarea b,
              dba_users c
        where a.sql_id = b.sql_id
          and a.user_id = c.user_id
        order by CNT desc) t,
      v$session s
where t.sql_id = s.sql_id(+);

查看正在执行sql的发起者的发放程序

--查看正在执行sql的发起者的发放程序
SELECT OSUSER     电脑登录身份,
      PROGRAM    发起请求的程序,
      USERNAME   登录系统的用户名,
      SCHEMANAME,
      B.Cpu_Time 花费cpu的时间,
      STATUS,
      B.SQL_TEXT 执行的sql
 FROM V$SESSION A
 LEFT JOIN V$SQL B
   ON A.SQL_ADDRESS = B.ADDRESS
  AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC

假设正在执行如下SQL，未提交

update bf_attach  t set   t.attach_id=123;

查出oracle当前的被锁对象

--查出oracle当前的被锁对象
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 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
  AND l.session_id = s.sid
ORDER BY sid, s.serial#;

kill掉当前会话对象

--kill掉当前的锁对象可以为
alter system kill session 'sid， s.serial#‘;
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

在有些时候，由于较大的事务或需要运行较长的SQL语句将导致需要kill的session并不能立即杀掉。对于这种情况将收到 "marked for kill"提示(如下)，一旦会话当前事务或操作完成，该会话被立即杀掉。