锁表后，解锁的办法

最新推荐文章于 2024-09-22 16:13:05 发布

jiemushe1814

最新推荐文章于 2024-09-22 16:13:05 发布

阅读量348

点赞数

分类专栏： Oracle_Knowledge 文章标签： session system kill object zk oracle

Oracle_Knowledge 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、查看被锁对象

select l.ORACLE_USERNAME "Oracle用户",
       l.OS_USER_NAME "系统用户",
       o.object_name "被锁对象",
       o.object_type "被锁对象类型",
       s.MACHINE "客户端机器码"
  from v$locked_object l, all_objects o, v$session s
 where l.OBJECT_ID = o.object_id
   and l.SESSION_ID = s.SID
   /*and o.OWNER in('ZK', 'CP')*/;

2、查询sid及serial#用于杀死锁表session

SELECT 'alter system kill session ''' || TA.SID || ',' || TA.SERIAL# || ''';',
       'alter system disconnect session ''' || TA.SID || ',' || TA.SERIAL# ||''' post_transaction immediate;',
       'host orakill ' || TC.instance_name || ' ' || TB.spid,
       'kill -9 ' || TB.spid,
       TB.spid,
       TA.osuser,
       TB.program,
       TA.terminal,
       TA.program
  FROM v$session TA, v$process TB, v$instance TC
 WHERE TB.addr = TA.paddr
   and ta.sid in (select s.sid
                    from v$locked_object l, all_objects o, v$session s
                   where l.OBJECT_ID = o.object_id
                     and l.SESSION_ID = s.SID

                     /*and o.OWNER in ('ZK', 'CP')*/)

3、执行第2步得出的语句： alter system kill session 'sid,serial#'，将session杀掉即可。可再执行第1步查看是否杀掉。