Oracle 解决存储过程包中，kill session的权限问题
一、问题展现
由于本人从事数据中心项目，数据中心有一个共享实例是对外提供数据的，6月11日发现数据库报ora_12516错误，一般ORA-12516有两个原因，一个是session数不够
，另一个就是客户端和服务端建立连接的时候频繁链接数据库，打开数据库链接而不关闭导致的。
通过plsq工具查询session，发行从6月9日到6月11日上午，有个用户频繁（每隔5分钟就要链接一次）的链接数据库，但是每次链接不能释放，导致用户该用户链接超过900多，正于是电话沟通业务厂家，让他们尽快排查关闭链接的功能(通过jdbc怀疑没有关闭链接功能或者关闭失效)，但是数据库不能停止，只有自己想办法（我不是dba，而是开发人员），想起以前给其他项目写过oracle单机处理杀掉无效进程的过程，于是试试。
二、处理步骤
1、查看数据库参数
1）查看当前数据库的processes设置
SQL> show parameter processes
NAME TYPE VALUE
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 1200
SQL> show parameter sessions
NAME TYPE VALUE
java_soft_sessionspace_limit integer 0l
icense_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 1500
shared_server_sessions integer
一般按照经验值，将processes数设置为1200，则sessions数必须为1.1*1200+5>=1325就行
数据库参数应该是够的，但是数据库不能轻易的改动参数，估计dba来了只有改参数了，但是改参数需要走流程，需要时间还得重启库（正式环境重启需要走流程），于是我觉得在等待业务厂家处理的同时，还是自己写个kill过程吧，毕竟万事不求人??。
2、编写存储过程（代码为本人知识产权）
在包头中定义：
CREATE OR REPLACE PACKAGE PKG_SYS IS
PROCEDURE PROC_KILL_INACTIVE_SESSIONS;
PROCEDURE SESSION_LOGS(P_SID IN NUMBER,
P_SERIAL IN NUMBER,
P_INST_ID IN NUMBER,
P_MODULE IN VARCHAR2,
P_STATUS IN VARCHAR2,
P_PROGRAM IN VARCHAR2,
P_MACHINE IN VARCHAR2,
P_LOGIN_TIME IN DATE,
P_MSG IN VARCHAR2,
P_OSUSER IN VARCHAR2);
END PKG_SYS;
/
在包体中编写
CREATE OR REPLACE PACKAGE BODY PKG_SYS IS
/*-------------------------------------------------------------------------------------*/
/* */
/* (C) Copyright IEDS Corporation 2017 All Rights Reserved. */
/* */
/* 函数名称 ：PROC_KILL_INACTIVE_SESSIONS */
/* 功能说明 ：杀无效进程 */
/* 参数说明 ： */
/* 参数 (I/O) 类型 说明 */
/* 返回值说明 ： */
/* 无 */
/* 详细说明 ： */
/* 维度频率 ：无 */
/* ORIGINAL : (1.0) 2017-05-25 CODED BY [IEDS] JINWEI */
/*-------------------------------------------------------------------------------------*/
PROCEDURE PROC_KILL_INACTIVE_SESSIONS AS
V_SID NUMBER;
V_SERIAL NUMBER;
V_INST_ID NUMBER;
V_MODULE VARCHAR2(100);
V_STATUS VARCHAR2(100);
V_PROGRAM VARCHAR2(100);
V_MACHINE VARCHAR2(100);
V_OSUSER VARCHAR2(100);
V_LOGIN_TIME DATE;
V_SQL VARCHAR2(1000);
V_PROC_MSG VARCHAR2(200);
VDAYS NUMBER;
CURSOR C is
select sid,
serial#,
inst_id,
module,
status,
program,
machine,
logon_time,
v.OSUSER
from gv$session v
where type != ‘BACKGROUND‘
and status IN (‘INACTIVE‘ /*,‘KILLED‘*/)
and (sysdate - v.LOGON_TIME) > VDAYS
and username = ‘share‘
and v.PROGRAM like ‘%JDBC%‘;
BEGIN
--无效jdbc链接天数
VDAYS := 3;
--打开游标
open C;
loop
BEGIN
fetch C
into V_SID,
V_SERIAL,
V_INST_ID,
V_MODULE,
V_STATUS,
V_PROGRAM,
V_MACHINE,
V_LOGIN_TIME,
V_OSUSER;
exit when C%notfound;
V_SQL := ‘alter system disconnect session ‘‘‘ || V_SID || ‘,‘ ||
V_SERIAL || ‘‘‘ immediate‘;
execute immediate V_SQL;
EXCEPTION
WHEN OTHERS THEN
V_PROC_MSG := ‘disconnect SESSION_SID=‘ || V_SID || ‘ 失败：‘;
V_PROC_MSG := V_PROC_MSG || ‘SQLCODE(‘ || TO_CHAR(SQLCODE) ||
‘) SQLERRM(‘ || SUBSTR(SQLERRM, 1, 128) || ‘)‘;
--异常日志
SESSION_LOGS(V_SID,
V_SERIAL,
V_INST_ID,
V_MODULE,
V_STATUS,
V_PROGRAM,
V_MACHINE,
V_LOGIN_TIME,
V_PROC_MSG,
V_OSUSER);
END;
--正常日志
V_PROC_MSG := ‘disconnect SESSION_SID=‘ || V_SID || ‘成功‘;
SESSION_LOGS(V_SID,
V_SERIAL,
V_INST_ID,
V_MODULE,
V_STATUS,
V_PROGRAM,
V_MACHINE,
V_LOGIN_TIME,
V_PROC_MSG,
V_OSUSER);
end loop;
close C;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END PROC_KILL_INACTIVE_SESSIONS;
--日志表
PROCEDURE SESSION_LOGS(P_SID IN NUMBER,
P_SERIAL IN NUMBER,
P_INST_ID IN NUMBER,
P_MODULE IN VARCHAR2,
P_STATUS IN VARCHAR2,
P_PROGRAM IN VARCHAR2,
P_MACHINE IN VARCHAR2,
P_LOGIN_TIME IN DATE,
P_MSG IN VARCHAR2,
P_OSUSER IN VARCHAR2) AS
-- 定义自动提交事务
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--删除3个月前日志
DELETE FROM JC_M_KILL_ORACLE_SESSION_LOGS T
WHERE T.NY <= TO_CHAR(ADD_MONTHS(SYSDATE, -3), ‘YYYYMM‘);
--插入异常日志
INSERT INTO JC_M_KILL_ORACLE_SESSION_LOGS
(sid,
serial,
inst_id,
module,
status,
program,
machine,
logon_time,
SYSTIME,
NY,
MSG,
OSUSER)
SELECT P_SID,
P_SERIAL,
P_INST_ID,
P_MODULE,
P_STATUS,
P_PROGRAM,
P_MACHINE,
P_LOGIN_TIME,
SYSDATE,
TO_CHAR(SYSDATE, ‘YYYYMM‘),
SUBSTR(P_MSG, 1, 200),
P_OSUSER
FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘SQLCODE(‘ || TO_CHAR(SQLCODE) || ‘) SQLERRM(‘ ||
SUBSTR(SQLERRM, 1, 128) || ‘)‘);
END SESSION_LOGS;
3、问题来了
1）问题1：
编译过程 报视图gv$session 在plsiq过程中不识别，缺乏权限。
原因:
Oracle为RAC集群机构，在单机可以，集群中gv$session和v$session都需要单独授权
于是通过操作系统用户root登录
su - oracle
sqlplus as / sysdba
grant select on gv$session to A ;
但是 gv$session不能直接授权，需要授权执行视图的同义词才行；
grant select on g_v$session to A ;
原因:
我们常用的v$ 是v_$的同义词，v_$是基于真正的视图v$，而真正的v$视图是在gv$的基础上限制inst_id得到;
我们常用的gv$是gv_$的同义词，gv_$基于真正的视图gv$，而真正的gv$视图基于系统表X$。
2）问题2：
继续编译，报错disconnect SESSION无权限；
su - oracle
sqlplus as / sysdba
GRANT ALTER SYSTEM TO A;
到此存储过程终于可执行了，但是还要增加定时自动执行JOB。
4、定时JOB
每天自动执行一次
begin
sys.dbms_scheduler.create_job(job_name => ‘JOB_PROC_KILL_INACTIVE_SESSIONS‘,
job_type => ‘PLSQL_BLOCK‘,
job_action => ‘PROC_KILL_INACTIVE_SESSIONS;‘,
start_date => to_date(‘2016-01-02 18:00:00‘,
‘yyyy-mm-dd hh24:mi:ss‘),
repeat_interval => ‘Freq=Day;Interval=1‘,
end_date => to_date(null),
job_class => ‘DEFAULT_JOB_CLASS‘,
enabled => true,
auto_drop => false);
end;
/