批量kill session实现脚本

在很多使用,因为各种原因,我们需要定时批量的kill一部分session,用来释放数据库部分资源,这里是因为bug导致temp不能正常释放,也可能是因为bug导致pga不释放,还有可能是因为太多inactive占用资源等等.我这里提供了两种方法来实现该功能
存储过程实现kill session

--创建记录表
CREATE TABLE kill_session_record
(
   kill_time        DATE,
   kill_statement   VARCHAR2 (1000)
)
/

--创建kill session存储过程
CREATE OR REPLACE PROCEDURE kill_inactive_session
IS
   CURSOR c
   IS
      SELECT sid, serial#
        FROM v$session s
       WHERE s.status = 'INACTIVE' AND s.username = 'XIFENFEI';

   k_sid      NUMBER;
   k_serial   NUMBER;
BEGIN
   OPEN c;

   FETCH c
   INTO k_sid, k_serial;

   WHILE c%FOUND
   LOOP
      BEGIN
         EXECUTE IMMEDIATE
               'ALTER SYSTEM DISCONNECT SESSION '''
            || k_sid
            || ','
            || k_serial
            || ''' IMMEDIATE';

         INSERT INTO kill_session_record (kill_time, kill_statement)
              VALUES (
                        SYSDATE,
                           'ALTER SYSTEM DISCONNECT SESSION '''
                        || k_sid
                        || ','
                        || k_serial
                        || ''' IMMEDIATE');
      EXCEPTION
         WHEN OTHERS
         THEN
            INSERT INTO kill_session_record (kill_time, kill_statement)
                 VALUES (
                           SYSDATE,
                              'Failure:ALTER SYSTEM DISCONNECT SESSION '''
                           || k_sid
                           || ','
                           || k_serial
                           || ''' IMMEDIATE');

            COMMIT;
      END;

      FETCH c
      INTO k_sid, k_serial;
   END LOOP;

   COMMIT;

   CLOSE c;
END;
/

--设置job定时运行
DECLARE
   job   NUMBER;
BEGIN
   sys.DBMS_JOB.submit (job,
                        what        => 'kill_inactive_session;',
                        next_date   => SYSDATE,
                        interval    => 'TRUNC(SYSDATE + 1) +7/24');
   COMMIT;
   DBMS_OUTPUT.put_line (job);
END;
/

如果是10GR2之前版本,需要把ALTER SYSTEM DISCONNECT SESSION 换成ALTER SYSTEM KILL SESSION

shell kill session

--shell脚本
# more kill_inactive_session.sh
#!/bin/sh
tmpfile0=/tmp/.kill_inactive_0
tmpfile1=/tmp/.kill_inactive_1
tmpfile2=/tmp/.kill_inactive_2
sqlplus / as sysdba <<EOF
spool $tmpfile1
select 'kill time:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') execute_time from dual;
select p.spid,s.sid,s.serial# from v\$process p,v\$session s
where s.paddr=p.addr
and username='XIFENFEI'
and s.status='INACTIVE';
spool off
EOF
cat $tmpfile1>>$tmpfile0
grep "^[0123456789]" $tmpfile1 |awk '{print $1}'>$tmpfile2
for x in `cat $tmpfile2`
do
kill -9 $x
done
rm $tmpfile1 $tmpfile2

--contab 调度
00 07 * * * /u01/script/kill_inactive_session.sh

两个脚本都可以在where中加一些限制条件,来实现你需要kill的会话.数据库级别kill相对系统级别来说更加温和点,建议优先考虑数据库级别kill session.如果要求立即释放资源,可能需要考虑系统级别.两中kill方式对于未提交且是inactive session都会被kill掉,然后回滚事务.

ORACLE 12C SQL语句中通过with 定义PL/SQL 函数

在ORACLE 12C支持在sql语句中编写函数,用来实现sql语句操作需要使用函数的部分功能,该功能对于你不想在数据库中新建函数 or 你的库是read only模式下要使用新函数实现某种功能,可以通过这种方法实现,增加了ORACLE数据库灵活点

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

测试过程创建一个简单函数,用来判断输入数据值是否是数字,如果是数字输出Y,如果不是输出N.
如果是12C以前的数据库,需要事先创建一个函数,然后通过select语句条用;但是在12C中直接通过一条select语句解决

SQL> with function Is_Number
  2    (x in varchar2) return varchar2 is
  3      Plsql_Num_Error exception;
  4       pragma exception_init(Plsql_Num_Error, -06502);
  5   begin
  6     if (To_Number(x) is NOT null) then
  7       return 'Y';
  8     else
  9       return '';
 10     end if;
 11   exception
 12    when Plsql_Num_Error then
 13      return 'N';
 14   end Is_Number;
 15  select is_number('www.orasos.com') is_num from dual;
 16  /

IS_NUM
--------------------------------------------------------------------------------
N