数据库查询锁脚本

2019独角兽企业重金招聘Python工程师标准>>>

DECLARE
  TYPE my_session_t IS TABLE OF v$session%ROWTYPE;
  lock_others_session my_session_t;
  be_locked_session   my_session_t;
  ls_output           VARCHAR2(255) := '';
  TYPE lock_others_t IS TABLE OF v$lock%ROWTYPE;
  lock_others lock_others_t;
  be_locked   lock_others_t;
BEGIN
  SELECT * BULK COLLECT
    INTO lock_others
    FROM v$lock
   WHERE BLOCK = 1;

  IF lock_others.count < 1 THEN
    dbms_output.put_line('目前数据库中没有被其他进程锁住的进程!');
  ELSE
    dbms_output.put_line('目前数据库中共有' || to_char(lock_others.count) ||
                         '个进程锁住别的进程!');
    dbms_output.put_line('');
    FOR i IN 1 .. lock_others.count
    LOOP
      SELECT * BULK COLLECT
        INTO lock_others_session
        FROM v$session
       WHERE sid = lock_others(i).sid;
      dbms_output.put_line('-----------------------------------------------------------------------------------------------------------------');
      dbms_output.put_line('第' || i || '个锁的进程信息:');
      ls_output := 'SID为' || lock_others_session(1).sid;
      ls_output := ls_output || '  ,登陆ORACLE帐号为 ' || lock_others_session(1)
                  .username;
      ls_output := ls_output || '  ,登陆操作系统帐号为 ' || lock_others_session(1)
                  .osuser;
      ls_output := ls_output || '  ,计算机名为 ' || lock_others_session(1)
                  .machine || ' ,';
      dbms_output.put_line(ls_output);
      ls_output := ' 正在进行的操作为 ' || to_nchar(lock_others_session(1).action); --使用TO_NCHAR来防止出现ORA-29275异常    
      ls_output := ls_output || ' 正在运行的模块为 ' || lock_others_session(1)
                  .module;
      dbms_output.put_line(ls_output);
      BEGIN
        SELECT sql_text
          INTO ls_output
          FROM v$sqltext
         WHERE hash_value = lock_others_session(1).sql_hash_value
           AND piece = 0;
        ls_output := '准备执行的SQL语句为 ' || ls_output; --使用TO_NCHAR来防止出现ORA-29275异常      
        dbms_output.put_line(ls_output);
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
      dbms_output.put_line('如果要杀掉该锁，请执行语句: ALTER SYSTEM KILL SESSION ''' || lock_others_session(1).sid || ',' || lock_others_session(1)
                           .serial# || ''';');
      SELECT * BULK COLLECT
        INTO be_locked
        FROM v$lock
       WHERE id1 = lock_others(i).id1
         AND id2 = lock_others(i).id2
         AND request > 0;
    
      dbms_output.put_line('');
      dbms_output.put_line('锁住了如下' || to_char(be_locked.count) || '个进程:');
    
      FOR j IN 1 .. be_locked.count
      LOOP
        SELECT * BULK COLLECT
          INTO be_locked_session
          FROM v$session
         WHERE sid = be_locked(j).sid;
      
        dbms_output.put_line('***********************************************');
        dbms_output.put_line('第' || j || '个被锁住的进程信息:');
        ls_output := 'SID为' || be_locked_session(1).sid;
        ls_output := ls_output || '  ,登陆ORACLE帐号为 ' || be_locked_session(1)
                    .username;
        ls_output := ls_output || '  ,登陆操作系统帐号为 ' || be_locked_session(1)
                    .osuser;
        ls_output := ls_output || '  ,计算机名为 ' || be_locked_session(1)
                    .machine || ' ,';
      
        dbms_output.put_line(ls_output);
        ls_output := ' 正在进行的操作为 ' || to_nchar(be_locked_session(1).action) || ' ,'; --使用TO_NCHAR来防止出现ORA-29275异常      
        ls_output := ls_output || ' 正在运行的模块为 ' || be_locked_session(1)
                    .module || ' ,';
        dbms_output.put_line(ls_output);
      
        BEGIN
          SELECT sql_text
            INTO ls_output
            FROM v$sqltext
           WHERE hash_value = be_locked_session(1).sql_hash_value
             AND piece = 0;
        
          ls_output := '正在执行的SQL语句为 ' || ls_output; --使用TO_NCHAR来防止出现ORA-29275异常        
          dbms_output.put_line(ls_output);
        
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;
      END LOOP;
    
      dbms_output.put_line('-----------------------------------------------------------------------------------------------------------------');
      dbms_output.put_line('');
    END LOOP;
  END IF;

EXCEPTION

  WHEN OTHERS THEN
    --通用显示错误信息  
    dbms_output.put_line(SQLERRM((-1) * SQLCODE));
END;

转载于:https://my.oschina.net/kanlianhui/blog/147180