杀远程指定主机超时session

需求描述:某客户短信系统需要连接到核心系统查询信息并进行发送。但是应用开发的有问题，导致db_link用了不释放。所以仅针对短信系统进行kill session动作作为备选方案。代码如下:

create  or  replace  procedure  kill_over_time_session(in_last_time  number )  is

   --此过程的目的是用于kill掉远程某些机器连接到当前数据库，并且超过一定时间不执行任务的session

   --解决此问题的办法是使用完dblink之后关闭该链接:alter session close database link <db_link_name>;

   /*配置写日志文件步骤:

  1.创建directtory(指定成你希望的路径):

     SQL>create directory user_dump as '/u01/app/oracle/admin/sample10/udump';

  2.在direcotry上进行授权:

     SQL>grant read,write on directory user_dump to dba;

  3.执行

     SQL>exec kill_over_time_session(xx),其中xx表示超时时间

  */

   --定义与sid,serial#有关参数

   TYPE  t_sid_cur  IS  REF  CURSOR ;  --定义存储sid,serial#的游标类型

  ref_sid_serial# t_sid_cur;  --定义sid,serial#的游标

   TYPE  t_sid_rec  IS  RECORD  (

    sid      NUMBER ,

    serial#  NUMBER );  --定义record 类型

  rec_sid_serial# t_sid_rec;  --申明传递sid,serial#的record变量

   --下面参数需要设置

  v_user_status  VARCHAR2 ( 20  ) := UPPER( 'INACTIVE' );  --希望kill用户的状态:ACTIVE,INACTIVE,SNIPED

   TYPE  t_machine_varry  IS  VARRAY  ( 1000 )  OF  varchar2  ( 64 );

   --下面参数需要设置

  vry_machine_list t_machine_varry := t_machine_varry(  'node1' ,  'node2'  );  --存储需要杀掉session的主机名,多主机名格式： t_machine_varry('node0','node1');

   --v_machine_list varchar2(100):='''node1'',''node2''';

  v_machine_list  varchar2 ( 5000  ) :=  '''' ;  --转存主机字符串

  v_last_time     NUMBER  := in_last_time;  --定义保持给定状态多长时间

   --定义sql语句

  v_slct_stmt  VARCHAR2 ( 5000  );  --此sql获取需要kill的session

  v_kill_stmt  VARCHAR2 ( 5000  );  --此sql为kill session的语句

   --定义trace文件

  v_file_name   varchar2 ( 100  ) :=  'all_killed_session.log' ;  --定义trace文件名

  v_trace_file UTL_FILE.FILE_TYPE;  --定义文件类型变量

   --定义其他参数

  v_count  NUMBER ;

BEGIN

   --初始化trace文件

  v_trace_file := utl_file.fopen(  'USER_DUMP' , v_file_name,  'a'  );

  utl_file.new_line(v_trace_file,  2 );

  utl_file.put_line(v_trace_file,

                     '************************************************Begin Kill: '  ||

                    to_char(  sysdate ,  'yyyy-mm-dd_hh:mi:ss'  ) ||

                     '************************************************'  ,

                     true );

   --utl_file.putf(v_trace_file,a,b,c);

   --utl_file.fflush(v_trace_file);

   --utl_file.fclose(v_trace_file);

   FOR  i  in  1  .. vry_machine_list.count  loop

    v_machine_list := v_machine_list || vry_machine_list(i) ||  ''',''' ;

 

   end  loop ;

  v_machine_list := substr(v_machine_list,  1 , length(v_machine_list) -  2 );

   --DBMS_OUTPUT.PUT_LINE(sysdate || ': Kill ' || v_user_status ||' session from ' || v_machine_list || ' over ' ||v_last_time || ' seconds');

  utl_file.put_line(v_trace_file,

                     'INFO: '  || to_char( sysdate  ,  'yyyy-mm-dd_hh:mi:ss' ) ||

                     ': Kill '  || v_user_status ||  ' session from '  ||

                    v_machine_list ||  ' over '  || v_last_time ||  ' seconds' ,

                     true );

  v_slct_stmt :=  'select sid,serial# from v$session where username is not null and machine in ('  ||

                 v_machine_list ||  ') and status ='''  || v_user_status ||

                  ''' and last_call_et >= '  || v_last_time;

   --DBMS_OUTPUT.PUT_LINE(v_slct_stmt);

  v_count :=  0 ;

   OPEN  ref_sid_serial#  FOR  v_slct_stmt;

   LOOP

     FETCH  ref_sid_serial#

       INTO  rec_sid_serial#;

     EXIT  WHEN  ref_sid_serial#% NOTFOUND ;

    v_kill_stmt :=  'alter system kill session '''  || rec_sid_serial#.sid ||  ','  ||

                   rec_sid_serial#.serial# ||  '''immediate' ;

     -- 记录总共kill掉的session数量

    v_count := v_count +  1 ;

     EXECUTE  IMMEDIATE  v_kill_stmt;

    utl_file.put_line(v_trace_file,

                       '    Killed Session SID: '  || rec_sid_serial#.sid ||

                       ' SERIAL#: '  || rec_sid_serial#.serial# ||

                       ' with sql:'  || v_kill_stmt,

                       true );

     --DBMS_OUTPUT.PUT_LINE('    Killed Session SID: ' || rec_sid_serial#.sid ||' SERIAL#: ' || rec_sid_serial#.serial# ||' with sql:' || v_kill_stmt);

   END  LOOP ;

   --DBMS_OUTPUT.PUT_LINE('All killed: ' || v_count || ' records!');

  utl_file.put_line(v_trace_file,

                     'All killed: '  || v_count ||  ' records this time!'  ,

                     true );

   CLOSE  ref_sid_serial#;

  utl_file.put_line(v_trace_file,

                     '************************************************End Kill: '  ||

                    to_char(  sysdate ,  'yyyy-mm-dd_hh:mi:ss'  ) ||

                     '************************************************'  ,

                     true );

  utl_file.fflush(v_trace_file);

  utl_file.fclose(v_trace_file);

EXCEPTION

   WHEN  OTHERS  THEN

     IF  ref_sid_serial#% ISOPEN  THEN

       CLOSE  ref_sid_serial#;

     END  IF  ;

     --DBMS_OUTPUT.PUT_LINE('ERROR: ' || SUBSTR(SQLERRM, 1, 200));

     IF  UTL_FILE.is_open(v_trace_file)  THEN

      utl_file.put_line(v_trace_file,  'ERROR: '  || SUBSTR( SQLERRM  ,  1 ,  200 ));

      utl_file.fclose(v_trace_file);

     ELSE

      v_trace_file := utl_file.fopen(  'USER_DUMP' , v_file_name,  'a'  );

      utl_file.put_line(v_trace_file,  'ERROR: '  || SUBSTR( SQLERRM  ,  1 ,  200 ));

      utl_file.fclose(v_trace_file);

     END  IF  ;

 

END  kill_over_time_session;