oracle批量解锁

最新推荐文章于 2023-05-17 14:47:27 发布

夜丶光

最新推荐文章于 2023-05-17 14:47:27 发布

阅读量314

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.youkuaiyun.com/lcconsider/article/details/100704248

版权

数据库专栏收录该内容

2 篇文章

订阅专栏

declare cursor mycur is
  SELECT SID,SERIAL# FROM (
    SELECT t2.username,t2.sid,t2.serial#,t3.object_name,t2.OSUSER,t2.MACHINE,
      t2.PROGRAM,t2.LOGON_TIME,t2.COMMAND,t2.LOCKWAIT,t1.LOCKED_MODE
    FROM v$locked_object t1, v$session t2, dba_objects t3
    WHERE t1.session_id = t2.sid
    AND t1.object_id = t3.object_id
    AND lockwait is not null
    AND LOGON_TIME<=TO_DATE('2019-09-10 08:30:00','yyyy-MM-dd HH24:mi:ss')
  ) A
  GROUP BY SID,SERIAL#
  ORDER BY SID;
begin
  for cur in mycur
  loop
    execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');
    DBMS_OUTPUT.PUT_LINE ( cur.sid || ' - ' || cur.SERIAL#);
  end loop;
end;