应用跑了一段时间后,偶尔发生System Runtime Error:
Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: ORA-00018: maximum number of sessions exceeded 错误,ORA-00018其实是oracle session及process占满的原因,解决方法:
1、加大process ,session数配置,用dba身份登陆oracle
A、find session hight water
SQL>
select
*
from v$session;--查看session占用情况
B、Current
parameter process and session
SQL> show parameter process
NAME
TYPE
VALUEITPUB
------------------------------------ ----------- ------------
------------------
aq_tm_processes
integer
0
db_writer_processes
integer
1
job_queue_processes
integer
0
log_archive_max_processes
integer
2
processes
integer
150
C、SQL> show parameter session
NAME
TYPE
VALUE
------------------------------------ ----------- ------------
------------------
java_max_sessionspace_size
integer
0
java_soft_sessionspace_limit
integer
0
license_max_sessions
integer
0ITPUB
license_sessions_warning
integer
0ITPUB
logmnr_max_persistent_sessions
integer
1
mts_sessions
integer
0
session_cached_cursors
integer
0
session_max_open_files
integer
10
sessions
integer
170
shared_server_sessions
integer
0
以上可以查看相关sesion及process占用的相关参数情况,下面设置加大session,process相关参数,一般来说sessions和processes有相关比例,sessions=processes*(1+10%)+5;
SQL> alter system set processes=300 scope=spfile;
SQL> alter system set sessions=335 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
2、一般来说process调置到300也能满足一般应用的情况了,如果还解决不了,可考虑适当加大,如果加大也无法解决,那可能需要检查你应用里面的数据库连接资源等释放情况了
Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: ORA-00018: maximum number of sessions exceeded 错误,ORA-00018其实是oracle session及process占满的原因,解决方法:
1、加大process ,session数配置,用dba身份登陆oracle
2、一般来说process调置到300也能满足一般应用的情况了,如果还解决不了,可考虑适当加大,如果加大也无法解决,那可能需要检查你应用里面的数据库连接资源等释放情况了
**************************************************************************
-
ORA-00018:maximum number of sessions exceeded解决方法原因:processes和sessions太小解决方法:调大processes和sessions,一般sessions=processes * 1.1 +5SQL> alter system set processes=300 scope=spfile; SQL> alter system set sessions=335 scope=spfile; SQL> shutdown immediate; SQL> startup; -
-