如何防止 Oracle SQL Developer 关闭数据库连接?

发布于 2024-08-02 16:11:05 字数 145 浏览 13 评论 0 原文

有什么方法可以阻止 Oracle SQL Developer 关闭我的数据库连接或增加超时吗?有时,在长时间运行的查询期间,SQL Dev 会关闭连接,让我的查询在服务器上运行,但没有结果。如果我通过 RDP 进入服务器并在本地运行 SQL Dev,它似乎永远不会出现此问题。

Is there any way to keep Oracle SQL Developer from closing my DB connections, or to increase the timeout? Sometimes during a long-running query SQL Dev will just close the connection, leaving my query running on the server but me with no results. If I RDP into the server and run SQL Dev locally it never seems to have this problem.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

长安忆 2024-08-09 16:11:05

回答

很可能是 SQL Developer 和数据库之间的防火墙造成了破坏。您可以使用 SQL Developer Keepalive 插件从 SQL Developer 解决该问题。

您还可以使用 Thomas大卫·曼

Oracle Net 可以配置死连接检测
(SQLNET.EXPIRE_TIME) 来解决此问题。设置 EXPIRE_TIME 为
将数据库服务器设置为小于防火墙连接的值
超时,以便 DCD 保持与数据库的连接处于活动状态。看
注释 151972.1“死连接检测 (DCD) 解释”

原始答案

我没有这个问题的答案,但我遇到了同样的问题。

我的 SQL Developer 和数据库之间的防火墙自动关闭“非活动”会话。根据防火墙的说法,长时间运行的查询是一个不活动的会话,因此他将其关闭。我还没有找到如何让 SQL Developer 通过长时间运行的查询的连接发送数据包,以便防火墙不会关闭连接。我不知道这是否可能。

所以我不认为这是一个SQL Developer问题,而是一个防火墙问题。

--
更新

SQL Developer 有一个扩展,可以让您保持连接活动:http://sites。 google.com/site/keepaliveext/

它还没有完全完成(例如,无论您指定什么超时,您收到的通知都会说明相同的超时),但它确实有效。我还没有针对最新的 SQL Developer 预发布版本对其进行测试,但它可以与 SQL Developer 2.2.x 配合使用

——
更新

对于 SQL Developer 4+,您可以使用: http://scristalli.github.io /SQL-Developer-4-keepalive/

Answer

It's most likely a firewall between SQL Developer and the database that breaks things. You can solve that from SQL Developer using the SQL Developer Keepalive plugin.

You can also fix this from the Database Server by using the answers by Thomas and David Mann.

Oracle Net can be configured with Dead Connection Detection
(SQLNET.EXPIRE_TIME) to workaround this problem. Set EXPIRE_TIME on
the database server to a value less than the firewall connection
timeout so that DCD keeps the connection to the database alive. See
Note 151972.1 "Dead Connection Detection (DCD) Explained"

Original answer

I don't have the answer for this, but I'm experiencing the same problem.

The firewall between my SQL Developer and the database automaticly closes "inactive" sessions. A long running query is according to the firewall an inactive session, so he closes it. I've not, yet, found how to make SQL Developer send packets over a connection with a long running query, so that the firewall doesn't close the connection. And I don't know if this is possible at all.

So I don't think it is a SQL Developer problem, but a firewall issue.

--
UPDATE

There is an extension for SQL Developer that allows you to keep the connections active: http://sites.google.com/site/keepaliveext/

It's not totally finished yet (for example the notification you get states the same timeout no matter what timeout you have specified) but it does the trick. I've not, yet, tested it against the latest SQL Developer pre-release, but it worked with SQL Developer 2.2.x

--
UPDATE

For SQL Developer 4+ you can use: http://scristalli.github.io/SQL-Developer-4-keepalive/

假情假意假温柔 2024-08-09 16:11:05

这是另一个可能有用的保持连接活动扩展。 (上述扩展程序包含许多已在此扩展程序中解决的问题。)

http://sites.google.com/site /keepconnext/

Here's another Keep Connection Active extension that might be of use. (The extension mentioned above contains a number of issues that are resolved in this extn.)

http://sites.google.com/site/keepconnext/

且行且努力 2024-08-09 16:11:05

对我来说也听起来像是防火墙问题。

您可能会幸运地在服务器的 SQLNET.ORA 文件中设置 EXPIRE_TIME 参数。从文档中:

使用参数 SQLNET.EXPIRE_TIME 指定发送探测以验证客户端/服务器连接是否处于活动状态的时间间隔(以分钟为单位)。设置大于 0 的值可确保连接不会由于客户端异常终止而无限期地保持打开状态。如果探测器发现已终止的连接或不再使用的连接,它将返回错误,导致服务器进程退出。该参数主要用于数据库服务器,它通常可以同时处理多个连接。

EXPIRE_TIME 的 10g 文档

Also sounds like a firewall problem to me.

You may have some luck with setting EXPIRE_TIME parameter in the server's SQLNET.ORA file. From the documentation:

Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

10g Documentation on EXPIRE_TIME

北笙凉宸 2024-08-09 16:11:05

这听起来不像是 SQL 开发人员的问题,当然我从来没有遇到过。您确定这不是其他东西,例如您的网络吗?如果您从桌面上通过 SQL plus 连接会发生什么。

This doesn't sound like an issue with SQL developer, cetainly I've never come across it. Are you sure it's not something else, like your network? What happens if you connect from SQL plus from your desktop.

旧伤慢歌 2024-08-09 16:11:05

作为一个临时解决方案,在“数据网格”一侧,您将找到“DBMS 输出”选项卡,打开 DBMS 输出并将轮询频率设置为合适的时间。

As a temporary solution to it, at the side of 'Data Grid' you'll find the tab 'DBMS Output', turn DBMS output ON and set the polling frequency to an agreeable time.

转身以后 2024-08-09 16:11:05

我们的 DBA 似乎已经找到了解决方案:

2.2。如果 OC4J 实例与 OC4J 实例之间存在防火墙;数据库
防火墙可能会删除与数据库服务器的非活动 jdbc 连接。但是,OC4J 实例无法检测到防火墙已使与数据库的连接超时。
Oracle Net 可以配置死连接检测 (SQLNET.EXPIRE_TIME) 来解决此问题。将数据库服务器上的 EXPIRE_TIME 设置为小于防火墙连接超时的值,以便 DCD 保持与数据库的连接处于活动状态。请参见注释 151972.1“死连接检测 (DCD) 解释”。由于这是在数据库服务器上配置的设置,而不是在中间层上配置的,因此它将应用于所有数据库连接(OCI 和瘦 JDBC)。
替代解决方案:
- 禁用或增加防火墙的空闲超时参数。
或者
- 将数据库和中间层服务器上的 TCP Keepalive 时间设置配置为小于防火墙连接超时的值。设置 TCP Keepalive 时间后,机器将在指定的分钟后重复发送空数据包以保持连接处于活动状态。由于每次在防火墙空闲超时内发送数据包,因此连接不会被关闭。

our DBA seems to have found a solution to this:

2.2. If there is a firewall present between the OC4J instance & the Database
The firewall might drop inactive jdbc connections to the database server. However, the OC4J instance cannot detect that the firewall has timed out the connection to the database.
Oracle Net can be configured with Dead Connection Detection (SQLNET.EXPIRE_TIME) to workaround this problem. Set EXPIRE_TIME on the database server to a value less than the firewall connection timeout so that DCD keeps the connection to the database alive. See Note 151972.1 "Dead Connection Detection (DCD) Explained". Since this is a setting configured on the database server, not on the midtier, it will apply for all database connections (OCI and thin JDBC).
Alternative solutions:
- Disable or increase the idle timeout parameter of the firewall.
or
- Configure the TCP Keepalive time setting on the database and on midtier servers to a value less than the firewall connection timeout. After setting the TCP Keepalive time, the machines will send repeatedly a null packet after the minutes specified to keep the connections alive. As the packet is sent every time inside the firewall idle timeout, the connection will not get closed.

长发绾君心 2024-08-09 16:11:05

#1.向 th3 DBA 检查所使用的连接的用户配置文件是什么。然后检查配置文件的不活动超时。

#2.将 SQL Developer 中的连接设置更改为 Connection Type=Basic 并使用 SID(而不是服务名称)。

如果您使用连接类型 = TNSNAMES 或服务名称而不是 SID - 连接将被删除。

#1. Check with th3 DBA what is the user's profile of the used connecting. Then check inactivity timeouts for the profile.

#2. Change the connection setting in SQL Developer to Connection Type=Basic and use SID (not Service Name).

Connection Properties in SQL Developer

If you use Connection Type=TNSNAMES or Service Name instead of SID - the connections will be dropped.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文