Oracle清理 v$session 失效session

oracle 服务器发出内存告警, 从日志来看, 前一天的数据库空余内存有差不多4个G, 但现在却还有不到2个 G, 看服务器进程发现 oracle 的进程比往常多出了500多个, 业务高峰..不可能, 半夜3点发出的告警.

看了一下 v$session中的 session ,kill 了不少的 sqlplus ,plsql 的 session, 内存降下了大约300M , 还是不对. 再看session状态, 有200多个 active, 剩下1000多个都是 inactive 的状态.

从结果来看,V$session 里面 inactive 的太多了,有的时间还有几个月以前的,google 的结果就是对这些session 进行清理。参考这里.

为了保险,增加一个对 last_call_et 的时间判断。LAST_CALL_ET是一个数字型(Number)字段,其含义是用户最后一条语句执行完毕后到sysdate的时间,单位为秒。每次用户执行一个新的语句后,该字段复位为0,重新开始记数。同时,将 Oracle 服务器上的 session 也排除在外。

方法一:由于kill session是直接将session kill掉,有可能出现导致事物回滚的现象,其实我们可以使用disconnect session完成当前事务并终止session。这种方式比alter system kill session跟安全可靠。

CREATE OR REPLACE PROCEDURE sys.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
 job_no number;
 num_of_kills number := 0;
 BEGIN
 FOR REC IN
 (SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
  FROM gv$session S
       WHERE S.USERNAME IS NOT NULL
   AND S.LAST_CALL_ET >= 2*60*60
   AND S.STATUS<>'KILLED'
   and s.OSUSER != 'oracle'
   ORDER BY INST_ID ASC
    ) LOOP
    DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
    execute immediate 'alter system disconnect session ''' || rec.sid || ', ' ||
   rec.serial# || '''immediate' ;
  DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
  num_of_kills := num_of_kills + 1;
  END LOOP;
   DBMS_OUTPUT.PUT_LINE ('Number of killed system sessions: ' || num_of_kills);
  END DB_KILL_IDLE_CLIENTS;
 /

但执行时有时会报错:
执行的时候会报错:

ERROR at line 1:
ORA-00031: session marked for kill
ORA-06512: at "SYS.DB_KILL_IDLE_CLIENTS", line 15
ORA-06512: at line 1

方法二:直接 kill 掉

CREATE OR REPLACE PROCEDURE DB_KILL_IDLE_CLIENTS2 AUTHID DEFINER AS
     job_no number;
     num_of_kills number := 0;
  BEGIN
     FOR REC IN
       (SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
          FROM gv$session S
              WHERE S.USERNAME IS NOT NULL
              and s.OSUSER != 'oracle'
                   AND S.LAST_CALL_ET >= 2*60*60
              AND S.STATUS= 'INACTIVE'
          ORDER BY INST_ID ASC
              ) LOOP
       execute immediate 'alter system kill session ''' || rec.sid || ', ' || rec.serial# || '''immediate' ;
           num_of_kills := num_of_kills + 1;
     END LOOP;
  END DB_KILL_IDLE_CLIENTS2;
 /

碰到的几个问题:

1. 普通用户没有权限访问 gv$session , 只有用 sys 执行,但 sys 密码又不记得了.

先修改sys密码
sqlplus / as sysdba;
alter user sys identified by *** 

然后再使用 sys 执行这个存储过程:

execute DB_KILL_IDLE_CLIENTS2;

2. 执行存储过程可能会失败,还需要对gv$session赋权

grant select on gv$session to xxx

会报错:

ORA-02030: can only select from fixed tables/views

oracle 中对带有$的文件,赋权使用使用"_"

grant select on gv_$session to xxx

3. 还有个问题没解决,修改完 sys 的密码后,使用 plsql 总是登录不进去,提示

ORA-01031: insufficient privileges

所以暂时通过命令行进入

sqlplus / as sysdba;

4. 创建存储过程的时候,直接粘贴,每次都会在最后多出来一串数字,而且又删不掉,然后就提示存储过程编译失败。

好在内容并不多,只好一行一行复制进去了。

5. 执行存储过程后效果很是明显,内存剩余从之前的1.8G增加到了4.1G,session 数从之前的1086下降到了188个,但不能每次都考这种方式来解决问题吧,估计还是应用有问题。

2015-08-31 22:5665