ORA-02391问题的解决方法

ORA问题的分析和解决其实是一个很好的学习思路，抓住一个每一个ORA错误，然后进一步分析一些原因，总结，总会有不一样的收获，还是那句话，任何问题背后都是有原因的。

今天早上，开发的同事反馈说客户端中抛出了ORA错误。

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

希望我们能够帮忙看看是什么原因，怎么修复一下。

这个问题其实还是比较清晰的，就是在我们设置的profile中会定义对应session数限制，比如存在用户test，sessions_per_user为50，则test用户最多使用50个session.

默认的profile是DEFAULT ,在创建数据库之后会做基本的初始化，比如密码的过期时间等等

SQL> select *from DBA_PROFILES WHERE PROFILE='DEFAULT'
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
默认的profile DEFAULT中sessions_per_user是Unlimited,但是实际中我们为了限制资源的使用，还是会自定义profile，其实还是基于profile DEFAULT
sql> select username,profile from dba_users where username=’ USER_TEST’;
USERNAME PROFILE
------------------------------ ------------------------------
USER_TEST APP_TEST
这个时候可以看到对应的最大session数就是20了。
SQL> select * from dba_profiles where profile='APP_TEST';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------ -------------------------------- -------- ----------------------------------------
APP_TEST COMPOSITE_LIMIT KERNEL DEFAULT
APP_TEST SESSIONS_PER_USER KERNEL 20
APP_TEST CPU_PER_SESSION KERNEL DEFAULT
APP_TEST CPU_PER_CALL KERNEL DEFAULT
APP_TEST LOGICAL_READS_PER_SESSION KERNEL DEFAULT
APP_TEST LOGICAL_READS_PER_CALL KERNEL DEFAULT
APP_TEST IDLE_TIME KERNEL DEFAULT
APP_TEST CONNECT_TIME KERNEL DEFAULT
APP_TEST PRIVATE_SGA KERNEL DEFAULT
APP_TEST FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
APP_TEST PASSWORD_LIFE_TIME PASSWORD DEFAULT
APP_TEST PASSWORD_REUSE_TIME PASSWORD DEFAULT
APP_TEST PASSWORD_REUSE_MAX PASSWORD DEFAULT
APP_TEST PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
APP_TEST PASSWORD_LOCK_TIME PASSWORD DEFAULT
APP_TEST PASSWORD_GRACE_TIME PASSWORD DEFAULT
基本知识介绍完毕。

如果抛出了ora-02391，我们可以通过alter user xxxxx profile xxxx sessions_per_user xxxx来进行解决。
问题是这种错误在数据库日志中不会显示，所以DBA也无从知晓，只能等待反馈，得到反馈后再解决问题。
我们可以变被动为主动。
使用下面的监控语句来进行检查。
比如我们设置阀值为90%,即每个user的对应的session超过profile中设定值的90%,就会返回结果。
select u.username||' with related profile '||p.profile||' has potential issue on '||p.resource_name ||' current value is '||s.cnt||' of '||p.limit ,
p.limit , s.cnt , p.profile
from dba_profiles p,
(select username,count(*) cnt from v$session where username is not null group by username) s,
dba_users u
where p.RESOURCE_NAME = 'SESSIONS_PER_USER'
and p.profile=u.profile
and s.username=u.username
--and p.profile !='DEFAULT'
and s.cnt*100/decode(p.limit,'DEFAULT',999,p.limit)>=90;
返回结果类似下面的形式。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 MPC with related profile DEFAULT has potential issue on SESSIONS_PER_USER current value is 5 of 100 100 5 DEFAULT
2 APCUSE with related profile DEFAULT has potential issue on SESSIONS_PER_USER current value is 5 of 100 100 5 DEFAULT
3 CLIFF with related profile DEFAULT has potential issue on SESSIONS_PER_USER current value is 16 of 100 100 16 DEFAULT
4 RPTMGR with related profile DEFAULT has potential issue on SESSIONS_PER_USER current value is 12 of 100 100 12 DEFAULT
5 M2P with related profile DEFAULT has potential issue on SESSIONS_PER_USER current value is 1 of 100 100 1 DEFAULT
6 GRACE with related profile SESSION_LIMIT3 has potential issue on SESSIONS_PER_USER current value is 30 of 120 120 30 SESSION_LIMIT3
7 RPTDB with related profile SESSION_LIMIT3 has potential issue on SESSIONS_PER_USER current value is 57 of 120 120 57 SESSION_LIMIT3

本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接：http://www.linuxidc.com/Linux/2015-08/122360.htm