oracle 通过代码获取文件 tnsnames.ora 的位置

Getting location of file tnsnames.ora by code

提问by RRUZ

How can I get the location of the tnsnames.orafile by code, in a machine with the Oracle client installed?

tnsnames.ora在安装了 Oracle 客户端的机器中，如何通过代码获取文件的位置？

Is there a windows registry key indicating the location of this file?

是否有 Windows 注册表项指示此文件的位置？

回答by Thomas Zoechling

Some years ago I had the same problem.
Back then I had to support Oracle 9 and 10 so the code only takes care of those versions, but maybe it saves you from some research. The idea is to:

几年前，我遇到了同样的问题。
当时我不得不支持 Oracle 9 和 10，所以代码只处理这些版本，但也许它可以使您免于进行一些研究。这个想法是：

• search the registry to determine the oracle client version
• try to find the ORACLE_HOME
• finally get the tnsnames from HOME

• 搜索注册表以确定 oracle 客户端版本
• 尝试找到 ORACLE_HOME
• 终于从 HOME 获取 tnsnames

---

---

public enum OracleVersion
{
    Oracle9,
    Oracle10,
    Oracle0
};

private OracleVersion GetOracleVersion()
{
    RegistryKey rgkLM = Registry.LocalMachine;
    RegistryKey rgkAllHome = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE\ALL_HOMES");

    /* 
     * 10g Installationen don't have an ALL_HOMES key
     * Try to find HOME at SOFTWARE\ORACLE\
     * 10g homes start with KEY_
     */
    string[] okeys = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE").GetSubKeyNames();
    foreach (string okey in okeys)
    {
        if (okey.StartsWith("KEY_"))
            return OracleVersion.Oracle10;
    }

    if (rgkAllHome != null)
    {
        string strLastHome = "";
        object objLastHome = rgkAllHome.GetValue("LAST_HOME");
        strLastHome = objLastHome.ToString();
        RegistryKey rgkActualHome = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\ORACLE\HOME" + strLastHome);
        string strOraHome = "";
        object objOraHome = rgkActualHome.GetValue("ORACLE_HOME");
        string strOracleHome = strOraHome = objOraHome.ToString();
        return OracleVersion.Oracle9;
    }
    return OracleVersion.Oracle0;
}

private string GetOracleHome()
{
    RegistryKey rgkLM = Registry.LocalMachine;
    RegistryKey rgkAllHome = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE\ALL_HOMES");
    OracleVersion ov = this.GetOracleVersion();

    switch(ov)
    {
        case OracleVersion.Oracle10:
            {
                string[] okeys = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE").GetSubKeyNames();
                foreach (string okey in okeys)
                {
                    if (okey.StartsWith("KEY_"))
                    {
                        return rgkLM.OpenSubKey(@"SOFTWARE\ORACLE\" + okey).GetValue("ORACLE_HOME") as string;
                    }
                }
                throw new Exception("No Oracle Home found");
            }
        case OracleVersion.Oracle9:
            {
                string strLastHome = "";
                object objLastHome = rgkAllHome.GetValue("LAST_HOME");
                strLastHome = objLastHome.ToString();
                RegistryKey rgkActualHome = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\ORACLE\HOME" + strLastHome);
                string strOraHome = "";
                object objOraHome = rgkActualHome.GetValue("ORACLE_HOME");
                string strOracleHome = strOraHome = objOraHome.ToString();
                return strOraHome;
            }
        default:
            {
                throw new Exception("No supported Oracle Installation found");
            }
    }
}

public string GetTNSNAMESORAFilePath()
{
    string strOracleHome = GetOracleHome();
    if (strOracleHome != "")
    {
        string strTNSNAMESORAFilePath = strOracleHome + @"\NETWORK\ADMIN\TNSNAMES.ORA";
        if (File.Exists(strTNSNAMESORAFilePath))
        {
            return strTNSNAMESORAFilePath;
        }
        else
        {
            strTNSNAMESORAFilePath = strOracleHome + @"\NET80\ADMIN\TNSNAMES.ORA";
            if (File.Exists(strTNSNAMESORAFilePath))
            {
                return strTNSNAMESORAFilePath;
            }
            else
            {
                throw new SystemException("Could not find tnsnames.ora");
            }
        }
    }
    else
    {
        throw new SystemException("Could not determine ORAHOME");
    }
}

回答by Colin Pickard

On Windows, the most likely locations are either %ORACLE_HOME%/network/adminor %TNS_ADMIN%(or the TNS_ADMIN registry setting). These two cover almost every installation.

在 Windows 上，最可能的位置是%ORACLE_HOME%/network/admin或%TNS_ADMIN%（或 TNS_ADMIN 注册表设置）。这两个几乎涵盖了所有安装。

Of course it is possible to have a working Oracle client without this file. Oracle has bewildering array of networking options, and there are plenty of ways to achieve a working setup with using TNSNAMES. Depending on what you are trying to achieve here, your first port of call might be the sqlnet.orafile, which is also found in %ORACLE_HOME%/network/admin. This should contain a line that looks something like this:

当然，没有该文件的 Oracle 客户端也可以工作。Oracle 有大量令人眼花缭乱的网络选项，并且有很多方法可以使用 TNSNAMES 实现工作设置。根据您在此处尝试实现的目标，您的第一个调用端口可能是该sqlnet.ora文件，该文件也位于%ORACLE_HOME%/network/admin. 这应该包含如下所示的一行：

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)

TNSNAMESmeans it will use the TNSNAMES.orafile (second in this case). LDAPand HOSTNAMEare alternate ways of resolving the database. If there is no TNSNAMESthe TNSNAMES.orafile will be ignored if it exists in the right place.

TNSNAMES意味着它将使用该TNSNAMES.ora文件（在本例中为第二个）。 LDAP并且HOSTNAME是解析数据库的替代方法。如果没有TNSNAMES，如果TNSNAMES.ora文件存在于正确的位置，则该文件将被忽略。

In C# / .NET this should get you the environment variables:

在 C#/.NET 中，这应该为您提供环境变量：

Environment.GetEnvironmentVariable("ORACLE_HOME");

Environment.GetEnvironmentVariable("ORACLE_HOME");

Environment.GetEnvironmentVariable("TNS_ADMIN");

Environment.GetEnvironmentVariable("TNS_ADMIN");

回答by GxG

List<string> logicalDrives = Directory.GetLogicalDrives().ToList();
            List<string> result = new List<string>();
            foreach (string drive in logicalDrives)
            {
                Console.WriteLine("Searching " + drive);
                DriveInfo di = new DriveInfo(drive);
                if(di.IsReady)
                    result = Directory.GetFiles(drive, "tnsnames.ora", SearchOption.AllDirectories).ToList();
                if (0 < result.Count) return;
            }
            foreach (string file in result) { Console.WriteLine(result); }

回答by RC.

I'm not a C# or a Windows guy for that matter so hopefully this helps. The tnsnames.ora file should be located in:

我不是 C# 或 Windows 人员，所以希望这会有所帮助。tnsnames.ora 文件应位于：

ORACLE_HOME\network\admin

If an alternate location has been specified, it should be available via the TNS_ADMIN registry key.

如果已指定备用位置，则应可通过 TNS_ADMIN 注册表项使用它。

See this linkfor more information on how Oracle handles tns names on Windows.

有关Oracle 如何在 Windows 上处理 tns 名称的更多信息，请参阅此链接。

回答by Benjamin Podszun

According to the net that depends on the version of Oracle and the working directory of the SQL*Plus process. This first linktells you the environment variable that specifies the base path for some versions (7, 8, 9i) of Oracle. If you use a different one, I'm sure there's a similar way to get to the system directory.

根据网络，取决于Oracle的版本和SQL*Plus进程的工作目录。第一个链接告诉您指定 Oracle 某些版本（7、8、9i）的基本路径的环境变量。如果您使用不同的方法，我相信有一种类似的方法可以进入系统目录。

If you spread versions of these files all over the place though and rely on the "look for a local tnsnames.ora first" behaviour of the client, then I guess you're out of luck.

如果您将这些文件的版本散布在各处并依赖客户端的“首先查找本地 tnsnames.ora”行为，那么我猜您不走运了。