• ORACLE 存储过程解及表解锁和停止执行


    查看进程:

    select * from v$process

    根据存储过程名称查找是否被锁:

    select * FROM dba_ddl_locks where name =upper('sp_1');

    select * FROM dba_ddl_locks where name like upper('%sp_1%');

    select sess.sid, 
        sess.serial#, 
        lo.oracle_username, 
        lo.os_user_name, 
        ao.object_name, 
        lo.locked_mode 
        from v$locked_object lo, 
        dba_objects ao, 
        v$session sess 
    where ao.object_id = lo.object_id and lo.session_id = sess.sid; 
    select  s.*
    from v$locked_object l, dba_objects o, v$session s, v$process p
     where l.object_id = o.object_id
       and l.session_id = s.sid
       and s.paddr = p.addr
       AND object_name= upper(''表或存储过程名字');
    

      

    查找SID:

    select t.sid,t.serial# from v$session t
    where t.sid=550;

    杀session解锁:

    alter system kill session '550,10047' immediate;

    --sid,t.serial#

    如果利用上面的命令杀死一个进程后,进程状态被置为"killed",但是锁定的资源很长时间没有被释放,那么可以在os一级再杀死相应的进程(线程),首先执行下面的语句获得进程(线程)号:
    select spid, osuser, s.program from v$session s,v$process p where 
    s.paddr=p.addr and s.sid=550 

    (550就是上面的sid)

    单机:
    SELECT A.PID, B.SID, B.SERIAL#, C.SQL_TEXT,A.SPID
      FROM V$PROCESS A, V$SESSION B, V$SQLAREA C
     WHERE A.ADDR = B.PADDR
       AND B.SQL_ADDRESS = C.ADDRESS
    
    rac:
    
    SELECT A.PID, B.SID, B.SERIAL#, C.SQL_TEXT,A.SPID
      FROM GV$PROCESS A, GV$SESSION B, GV$SQLAREA C
     WHERE A.ADDR = B.PADDR
       AND B.SQL_ADDRESS = C.ADDRESS

    再到linux下 kill -9 pid

    杀会话

    select  a.USERNAME,a.MACHINE, sql_text,'alter system kill session '''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||'''immediate;' ,status 
    from gV$session a inner join GV$sql b on a.sql_id=b.sql_id 
    WHERE status = 'ACTIVE'

    定时任务,杀掉特定服务器上执行超过一个小时的语句

    create or replace procedure ks_kill_sp_timeout
        /******************************************************************
          存储过程名称:
          存储过程内容:监控存储过程是否正常
          作者姓名: 
          编写时间: 2017年4月17日
          输入参数:
    
          输出参数:
        *******************************************************************/
    as
        v_sql varchar2(2000);
        v_count number;
    begin
    
    select count(distinct 'alter system kill session '''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||''' immediate;')  into v_count
    from gV$session a inner join GV$sql b on a.sql_id=b.sql_id
    WHERE a.status = 'ACTIVE'
    and a.MACHINE = 'db'
    and ceil( (sysdate - a.SQL_EXEC_START) * 24 * 60) >= 60 ;
    
    if v_count > 0 then
      select distinct 'alter system kill session '''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||''' immediate;'  into v_sql
      from gV$session a inner join GV$sql b on a.sql_id=b.sql_id
      WHERE a.status = 'ACTIVE'
      and a.MACHINE = 'db'
      and ceil( (sysdate - a.SQL_EXEC_START) * 24 * 60) >= 60 ;
      
      dbms_output.put_line(v_sql);  
      execute immediate v_sql;
    else
      dbms_output.put_line(v_count);
    end if;
    
    
    
    
    end;
  • 相关阅读:
    结对编程
    个人项目(JUnit单元测试)
    我的第一个GitHub仓库
    Visual Studio 创建C++或C#Windows程序
    字符串操作
    练习数值计算
    Hello World
    ceph部署出现错误及解决
    asp.net mvc 用Redis实现分布式集群共享Session。
    Unable to load DLL 'rasapi32.dll': 动态链接库(DLL)初始化例程失败。
  • 原文地址:https://www.cnblogs.com/linn/p/4208893.html
Copyright © 2020-2023  润新知