C#无法连接Oracle XE数据库，连接超时问题求助

问题现状 #

使用Oracle.ManagedDataAccess.Client组件连接Oracle 21c数据库时持续超时，但SQL Developer可正常连接；lsnrctl status显示监听服务正常运行。

涉事代码 #

private const string _connectionString = "User Id=c##BookingApp;Password=ftn;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))";

public static void Test()
{
    using (OracleConnection con = new OracleConnection(_connectionString))
    {
        try
        {
            con.Open();

            OracleCommand com = con.CreateCommand();
            com.CommandText = "SELECT * FROM user_tb;";
            com.ExecuteToStream(System.IO.Stream.Null);

            con.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }
    }
}

监听服务状态（lsnrctl status输出） #

LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 25-MAR-2024 10:42:12

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-08F6LQM)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date                15-MAR-2024 15:56:49
Uptime                    9 days 18 hr. 45 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\app\PC\product\21c\homes\OraDB21Home1\network\admin\listener.ora
Listener Log File         C:\app\PC\product\21c\diag\tnslsnr\DESKTOP-08F6LQM\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-08F6LQM)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-08F6LQM)(PORT=5500))(Security=(my_wallet_directory=C:\APP\PC\PRODUCT\21C\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "7c74ac5f71294090a35b32d2635fdfc9" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
The command completed successfully

解决步骤 #

• 修正连接字符串的HOST地址：监听绑定的是机器名DESKTOP-08F6LQM，而非localhost，将连接字符串中的HOST=localhost改为HOST=DESKTOP-08F6LQM，或直接使用127.0.0.1（避免DNS解析问题）。
• 核对服务名是否匹配：确认SQL Developer连接的是CDB（XE）还是PDB（xepdb1），如果数据表user_tb位于PDB中，需将连接字符串的SERVICE_NAME=xe改为SERVICE_NAME=xepdb1。
• 添加超时参数：在连接字符串中增加Connection Timeout=30（可根据情况调整时长），避免因默认超时过短导致连接失败。
• 使用EZConnect简化格式：改用更简洁的连接字符串格式，减少配置错误：

  User Id=c##BookingApp;Password=ftn;Data Source=DESKTOP-08F6LQM:1521/XE
  

• 匹配驱动版本：确保NuGet安装的Oracle.ManagedDataAccess版本与Oracle服务器版本（21c）一致，优先选择21.x系列的稳定版本。
• 关于Oracle.DataAccess.Client：该组件是旧版非托管驱动，已被Oracle.ManagedDataAccess.Client替代，无需刻意寻找，专注维护托管驱动即可。

内容的提问来源于stack exchange，提问作者Cutthroat