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个,但不能每次都考这种方式来解决问题吧,估计还是应用有问题。