如何通过 PL/SQL Developer 9 访问本地安装的 Oracle 11 g

How to acess locally installed Oracle 11 g throught PL/SQL Developer 9

提问by Tnsman

I need to develop in PL SQL using PL/SQL developer but I don't have idea how to make both oracle 11g and pl/sql developer work in the same machine because pl/sql developer need 32 bit oracle client installed when oracle did not require client when we work in local machine. This is what I did:

我需要使用 PL/SQL developer 在 PL SQL 中进行开发，但我不知道如何让 oracle 11g 和 pl/sql developer 在同一台机器上工作，因为 pl/sql developer 需要在 oracle 没有安装时安装 32 位 oracle 客户端当我们在本地机器上工作时需要客户端。这就是我所做的：

1) I installed oracle 11g 64 bit Enterprise on drive d:\ 2) Then I installed oracle client 32 bit on drive c:(pl/sql developer can work only with 32 bit version of oci.dll) 3) After I went to the client_home%->Network->Admin folder and corrected tnsname.ora file and wrote there my connection setting which was copied from my oracle 11 g home_dir..tnsnames.ora

1) 我在驱动器 d:\ 上安装了 oracle 11g 64 bit Enterprise 2) 然后我在驱动器 c 上安装了 oracle client 32 bit:(pl/sql developer can only work with 32 bit version of oci.dll) 3) 在我去之后client_home%->Network->Admin 文件夹并更正了 tnsname.ora 文件并在那里写入了我的连接设置，该设置是从我的 oracle 11 g home_dir..tnsnames.ora 复制的

    MyOracle =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
)

After I installed PL/SQL developer in c:\plsqldev\ and rebooted my machine.

在我在 c:\plsqldev\ 中安装 PL/SQL developer 并重新启动我的机器之后。

So now I can connect to my database with sql*plus located in my oracle database directory , but when I try to connect to my oracle with my client sql*plus it says tns protocol error when I try to connect using pl/sql developer it says could not resolve the connection identifier specified. What should I do? Could you help me please

所以现在我可以使用位于我的 oracle 数据库目录中的 sql*plus 连接到我的数据库，但是当我尝试使用我的客户端 sql*plus 连接到我的 oracle 时，当我尝试使用 pl/sql developer 连接时，它说 tns 协议错误说无法解析指定的连接标识符。我该怎么办？请问你能帮帮我吗

Added:I erased TNSNAMES parameter from NAMES.DIRECTORY_PATH (in both - server and client sqlnet.ora files) and now when i login it says: ora-12154 tns could not resolve the connect identifier specified , but it still sucessfully connecting to DB when i try to connect with SERVER - s sql*plus.

补充：我从 NAMES.DIRECTORY_PATH 中删除了 TNSNAMES 参数（在服务器和客户端 sqlnet.ora 文件中），现在当我登录时它说：ora-12154 tns 无法解析指定的连接标识符，但它仍然成功连接到数据库时我尝试连接 SERVER - s sql*plus。

Here is my config files:

这是我的配置文件：

**servers listener.ora **

        SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = D:\app\Light\product.2.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Light\product.2.0\dbhome_1\bin\oraclr11.dll")
        )
      )

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
      )
ADR_BASE_LISTENER = D:\app\Light

servers sqlnet.ora

服务器 sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (HOSTNAME)

servers tnsname.ora

服务器 tnsname.ora

 LISTENER_ORCL =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )              

Clients sqlnet.ora

客户端 sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (HOSTNAME)

Clients tnsnames.ora

客户端 tnsnames.ora

    ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ADD:Yess!)) Finally i found the solution: :) We need to use server's config file instead of clients one - but the oci.dll(32bit) must be taken from clients directory. I will describe the basic steps - how i made pl/sql developer 10.0.2 work with locally installed Oracle 11.2 x64:

ADD:Yess!)) 最后我找到了解决方案： :) 我们需要使用服务器的配置文件而不是客户端的配置文件 - 但 oci.dll（32 位）必须从客户端目录中获取。我将描述基本步骤 - 我如何使 pl/sql developer 10.0.2 与本地安装的 Oracle 11.2 x64 一起工作：

1) Fist we need to install Oracle (I installed it to d:\app)

1）首先我们需要安装Oracle（我安装到d:\app）

2)Then wee need to download and install oracle instantclient (i founded that there are not any config files (not even network->admin directory) in the instant client , just needed dll's like oci.dll and etc.. note: the first time i've choosen "administrator" one which have it's own config file inside, seems this kind of client is needed only for remote access to Oracle). I installed(unpacked) my instant client to c:\app\ ;

2）然后我们需要下载并安装oracle即时客户端（我发现即时客户端中没有任何配置文件（甚至没有network->admin目录），只需要像oci.dll等dll。注意：我第一次选择了“管理员”，里面有自己的配置文件，似乎只有远程访问 Oracle 才需要这种客户端）。我安装（解压）我的即时客户端到 c:\app\ ；

3) Open PL/SQL developer , go to tools->preferences->connection and set the "Oracle Home" filed to the home directory of your instant client (I puted c:\app), but usually it already exist in the drop down list and we just need to click the drop down list and choose OraClient11g_home1_32bit , by this way i set the oci library path(choosen from drop down list). Click apply and exit from pl/sql developer;

3）打开PL/SQL developer，进入tools->preferences->connection，将“Oracle Home”设置为你的instant client的home目录（我放的是c:\app），但是通常drop里面已经存在了下拉列表，我们只需要单击下拉列表并选择 OraClient11g_home1_32bit ，通过这种方式我设置了 oci 库路径（从下拉列表中选择）。点击apply并退出pl/sql developer；

4) Now we need to create two environment variables(the fist one will show the path to folder with connection configs , the second the language(if i'm not right please correct me)) ->

4）现在我们需要创建两个环境变量（第一个将显示带有连接配置的文件夹的路径，第二个是语言（如果我不对请纠正我））->

• TNS_ADMIN = %SERVER_HOME%\NETWORK\ADMIN\ (i puted D:\app\Light\product\11.2.0\dbhome_1\NETWORK\ADMIN)

• TNS_LANG = AMERICAN_CIS.CL8MSWIN1251

• TNS_ADMIN = %SERVER_HOME%\NETWORK\ADMIN\（我把 D:\app\Light\product\11.2.0\dbhome_1\NETWORK\ADMIN）

• TNS_LANG = AMERICAN_CIS.CL8MSWIN1251

5) Reboot (optionally) and then start pl/sql developer - there must be three connection alias , i choosed orcl , entered my login ,password and hiited OK and everything worked )));

5）重新启动（可选）然后启动pl/sql developer - 必须有三个连接别名，我选择了 orcl ，输入我的登录名，密码并点击OK，一切正常）））；

Thank's to everyone who answered in this topic, hope it'll help someone

感谢所有回答此主题的人，希望它会帮助某人

回答by veeru

check the port name in tns file it sholud be 1521 and where XE is my local db name like this E = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Shantu)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) )

检查 tns 文件中的端口名称，它应该是 1521，其中 XE 是我的本地数据库名称，例如 E = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Shantu)(PORT = 1521)) (CONNECT_DATA = (SERVER) = 专用）（SERVICE_NAME = XE））

)

)

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )

ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )