`
dannyhz
  • 浏览: 415255 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
文章分类
社区版块
存档分类
最新评论

kill machine function

阅读更多
create or replace
PROCEDURE        gce_kill_machine (p_machine IN VARCHAR2, p_timeout_minutes IN NUMBER DEFAULT 30)
IS
   l_inst_id           NUMBER;
   l_sid               NUMBER;
   l_serial            NUMBER;
   lv_login_minutes    NUMBER;
   l_sql               VARCHAR2 (32000);
   l_session_user      VARCHAR2 (64);
   l_status            VARCHAR2(8);
   l_osuser            VARCHAR2(30);
   l_process           VARCHAR2(24);
   l_machine           VARCHAR2(64);
   l_port              NUMBER;
   l_program           VARCHAR2(48);
   l_module            VARCHAR2(64);
   l_logon_time        date;
   l_event             VARCHAR2(64);
   l_sql_id            VARCHAR2(13);
   l_prev_sql_id       VARCHAR2(13);
   l_sql_text          VARCHAR2(1000);
   l_prev_sql_text     VARCHAR2(1000);
   l_kill_user         VARCHAR2(30);
   l_kill_os_user      VARCHAR2(30);
   l_kill_machine      VARCHAR2(64);
   l_kill_program      VARCHAR2(48);
   cur_sessions        sys_refcursor;
BEGIN

   l_kill_user := sys_context('USERENV','SESSION_USER');
   l_kill_os_user := sys_context('USERENV','OS_USER');
   l_kill_machine := sys_context('USERENV','HOST');
   l_kill_program := sys_context('USERENV','MODULE');

   -- query the information of the session to be killed
   open cur_sessions for
     SELECT inst_id,sid,serial#,username,status,osuser,process,machine,port,program,module,logon_time,
            event,sql_id,prev_sql_id
     FROM gv$session gs
     WHERE username='OPGCEP2'
       AND machine=p_machine
       AND STATUS!='KILLED'
       AND audsid!=USERENV('SESSIONID')
       AND not exists (select distinct inst_id, sid
                       from gv$mystat gm
                       where gm.inst_id=gs.inst_id and gm.sid=gs.sid
                      );
   LOOP
     FETCH cur_sessions
     INTO l_inst_id,l_sid,l_serial,l_session_user,l_status,l_osuser,l_process,l_machine,l_port,l_program,
          l_module,l_logon_time,l_event,l_sql_id,l_prev_sql_id;
     EXIT WHEN cur_sessions%NOTFOUND;

     if l_sql_id is not null then
       begin
         SELECT sql_text into l_sql_text
         FROM gv$sql
         WHERE child_number = 0
         AND inst_id = l_inst_id
         AND sql_id = l_sql_id;
       exception
         when others then
           l_sql_text := '';
       end;
     end if;

     if l_prev_sql_id is not null then
       begin
         SELECT sql_text into l_prev_sql_text
         FROM gv$sql
         WHERE child_number = 0
         AND inst_id = l_inst_id
         AND sql_id = l_prev_sql_id;
       exception
         when others then
           l_prev_sql_text := '';
       end;
     end if;

     -- ONLY ALLOW sessions which logged in p_timeout_minutes(such as 30) minutes ago to be killed
     lv_login_minutes := (sysdate - l_logon_time)*24*60;
     IF lv_login_minutes > p_timeout_minutes
     THEN
        merge into GCE_MAINT.KILL_SESSION_LOG T0
        using(select l_inst_id inst_id,l_sid sid,l_serial serial#,l_kill_user kill_user,l_kill_os_user kill_os_user,l_kill_machine kill_machine,l_kill_program kill_program
                     ,sysdate kill_time,l_session_user session_user,l_status status,l_osuser os_user,l_process process,l_machine machine,l_port port,l_program program,l_module module
                     ,l_logon_time logon_time,l_event event,l_sql_id sql_id,l_prev_sql_id prev_sql_id,l_sql_text sql_text,l_prev_sql_text prev_sql_text from dual) T1
        on (T0.inst_id=T1.inst_id and T0.sid=T1.sid and T0.serial#=T1.serial# and T0.logon_time=T1.logon_time)
        when matched then
          update set t0.kill_user=t1.kill_user, t0.kill_os_user=t1.kill_os_user, t0.kill_machine=t1.kill_machine
          , t0.kill_program=t1.kill_program, t0.kill_time=t1.kill_time, t0.username=t1.session_user, t0.status=t1.status
          , t0.osuser=t1.os_user, t0.process=t1.process, t0.machine=t1.machine, t0.port=t1.port, t0.program=t1.program
          , t0.module=t1.module, t0.event=t1.event, t0.sql_id=t1.sql_id
          , t0.prev_sql_id=t1.prev_sql_id, t0.sql_text=t1.sql_text, t0.prev_sql_text=t1.prev_sql_text
        when not matched then
          insert(
          INST_ID
          ,SID
          ,SERIAL#
          ,KILL_USER
          ,KILL_OS_USER
          ,KILL_MACHINE
          ,KILL_PROGRAM
          ,KILL_TIME
          ,USERNAME
          ,STATUS
          ,OSUSER
          ,PROCESS
          ,MACHINE
          ,PORT
          ,PROGRAM
          ,MODULE
          ,LOGON_TIME
          ,EVENT
          ,SQL_ID
          ,PREV_SQL_ID
          ,SQL_TEXT
          ,PREV_SQL_TEXT
        )
        values(
          l_inst_id
          ,l_sid
          ,l_serial
          ,l_kill_user
          ,l_kill_os_user
          ,l_kill_machine
          ,l_kill_program
          ,sysdate
          ,l_session_user
          ,l_status
          ,l_osuser
          ,l_process
          ,l_machine
          ,l_port
          ,l_program
          ,l_module
          ,l_logon_time
          ,l_event
          ,l_sql_id
          ,l_prev_sql_id
          ,l_sql_text
          ,l_prev_sql_text
        );
        COMMIT;

        -- FORMAT OF KILL SESSION statement
        -- e.g. -- alter system kill session '18,21349,@3' immediate;
        l_sql :=
              'alter system kill session '''
           || TO_CHAR (l_sid)
           || ', '
           || TO_CHAR (l_serial)
           || ', @'
           || TO_CHAR (l_inst_id)
           || ''' immediate';
        DBMS_OUTPUT.PUT_LINE (l_sql);

        -- Actually KILL session now: e.g. alter system kill session '4, 47438, @1' immediate
        EXECUTE IMMEDIATE (l_sql);
     ELSE
        DBMS_OUTPUT.
        PUT_LINE ('*** ERROR: Cannot KILL session ('||to_char(l_sid)||','||to_char(l_serial)||',@'||to_char(l_inst_id)||') since it just logged in '|| to_char(lv_login_minutes) ||' minutes! ***');
     END IF;
   END LOOP;
   CLOSE cur_sessions;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR: No such session');
      ROLLBACK;
      RAISE;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR: '||substr(SQLERRM,1,1000));
      ROLLBACK;
      RAISE;
END;
分享到:
评论

相关推荐

    linux全志R16的linux系统编译的资料_20170502_1655.7z

    You must install 'makeinfo' on your build machine makeinfo is usually part of the texinfo package in your distribution make: *** [dependencies] 错误 1 make:离开目录“/home/wwt/linux_r16/lichee/...

    联通M2M-APN网络远程终端管理

    - `sudo squid3 -k function`:执行一些管理功能,如`parse`(解析squid.conf)、`shutdown`(关闭squid进程)、`interrupt`(立即关闭squid)、`kill`(发送kill信号给squid)等。 - `sudo squid3 -D`:禁用启动时...

    Practical Mod Perl

    Using kill to Control Processes Section 5.4. Using apachectl to Control the Server Section 5.5. Validating Server Configuration Section 5.6. Setuid root Startup Scripts Section ...

    orcale常用命令

    SQL>select object_name,status from user_objects where object_type='FUNCTION'; SQL>select object_name,status from user_objects where object_type='PROCEDURE'; 查看函数和过程的源代码 SQL>select text...

    VB编程资源大全(英文源码 文件)

    Use Kill to remove the original file and then use Name to give the temporary file the old file name."<END><br>8 , cpyfile.zip This will copy a specified file to a directory. It also shows its ...

Global site tag (gtag.js) - Google Analytics