ORA-02399

具体错误信息为

ORA-02399: exceede maximum connect time, you are being logged off

大概含义为：超过了最大连接时间，你被强行登出

出现原因

首先看一下Oracle CONNECT_TIME参数是多少

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME','CONNECT_TIME');

本案列中的设置值

PROFILE|RESOURCE_NAME            |RESOURCE_TYPE|LIMIT              |
-------|-------------------------|-------------|-------------------|
DEFAULT|IDLE_TIME                |KERNEL       |14                 |
DEFAULT|CONNECT_TIME             |KERNEL       |10                 |

CONNECT_TIME: Parameter will disconnect a session whose connect time exceed the value for connect_time.The connect_time is expressed in minutes.

CONNECT_TIME: 会断开超过CONNECT_TIME时间的会话，单位是分钟，默认应该是UNLIMITED

IDLE_TIME: Permitted periods of continuous inactive time during a session(minutes).

IDLE_TIME: 在会话期间允许的连续不活动时间，单位也是分钟，默认是10分钟

所以原因很简单，CONNECT_TIME默认为无限制被设置成为10分钟，所以每隔十分钟数据库连接就会被强行登出(不管你有没有在执行SQL)，所以导致连接不可用就会报错ORA-02399

解决

改数据库参数

将两个值改成无限或者长一点

ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME UNLIMITED;

改连接池参数

Hikari连接池max-lifetime

用来设置一个connection在连接池中的存活时间，默认是1800000，即30分钟。如果设置为0，表示存活时间无限大。如果不等于0且小于30秒则会被重置回30分钟。

# 单位毫秒
max-lifetime: 300000

普通数据库连接池设置maxAge参数(比如org.apache.tomcat.jdbc.pool.DataSource)

<property name="maxAge" value="300000"/>

看下参数的含义

/**
 * Time in milliseconds to keep this connection alive even when used.
 * When a connection is returned to the pool, the pool will check to see if the
 * ((now - time-when-connected) &gt; maxAge) has been reached, and if so,
 * it closes the connection rather than returning it to the pool.
 * The default value is 0, which implies that connections will be left open and no
 * age check will be done upon returning the connection to the pool.
 * This is a useful setting for database sessions that leak memory as it ensures that the session
 * will have a finite life span.
 * @param maxAge the time in milliseconds a connection will be open for when used
 */
public void setMaxAge(long maxAge);

参考

https://smarttechways.com/2018/12/05/connect_time-and-idle_time-in-oracle-profile/

Java_Oracle数据库连接池频繁出现ORA-02399错误