PLSQL连接本地oracle或远程oracle数据库实现随意切换功能

前言

先赞后看，此生必赚！

每次安装oracle以后，都会出现使用plsql连接不上的问题！多次重启电脑、重装系统的磨人经历之后，

今天看到这篇文章算是你我之幸！希望能帮助广大技术人员减少一些时间，顺利进行连接！

注：也可以用plsql连接远程数据库（只要有oracle的network\admin\tnsnames.ora就行）。

首先下载64位oracle以及32位轻量级客户端(注意版本的对应，我用的是11g的oracle和11.2的客户端)：

点击进入oracle下载页面点击进入客户端下载页面

以下步骤请自行更改路径，不然，出错了别怪我哦：

step1 复制network文件

oracle安装到某路径下，安装步骤很简单，网上可以搜到！

客户端随便解压到一个路径，我的是放在d:\app\shuhao\instantclient_11_2了。

然后把oracle中的d:\app\shuhao\product\11.2.0\dbhome_1里面的整个network文件夹复制，

粘贴到客户端文件夹d:\app\shuhao\instantclient_11_2下面，如下图所示：

step2 修改配置文件

2.1 修改oracle数据库文件

d:\app\shuhao\product\11.2.0\dbhome_1\network\admin\listener.ora文件内容，添加一个sid_desc：

# listener.ora network configuration file: d:\app\shuhao\product.2.0\dbhome_1\network\admin\listener.ora
# generated by oracle configuration tools.
 
sid_list_listener =
  (sid_list =
    (sid_desc =
      (sid_name = clrextproc)
      (oracle_home = d:\app\shuhao\product.2.0\dbhome_1)
      (program = extproc)
      (envs = "extproc_dlls=only:d:\app\shuhao\product.2.0\dbhome_1\bin\oraclr11.dll")
    )
#下面的sid_desc是新增加的内容，注意修改oracle_home路径
    (sid_desc =
      (sid_name = orcl)
      (oracle_home = d:\app\shuhao\product.2.0\dbhome_1)
      (global_dbname = orcl)
    )
  )
 
listener =
  (description_list =
    (description =
      (address = (protocol = tcp)(host = localhost)(port = 1521))
    )
  )
 
adr_base_listener = d:\app\shuhao

2.2 修改oracle数据库文件

d:\app\shuhao\product\11.2.0\dbhome_1\network\admin\tnsnames.ora文件内容，源文件没有做改动：

# tnsnames.ora network configuration file: d:\app\shuhao\product.2.0\dbhome_1\network\admin\tnsnames.ora
# generated by oracle configuration tools.
 
oraclr_connection_data =
  (description =
    (address_list =
      (address = (protocol = tcp)(host = localhost)(port = 1521))
    )
    (connect_data =
      (service_name = orcl)
    )
  )
 
listener_orcl =
  (address = (protocol = tcp)(host = localhost)(port = 1521))
 
orcl =
  (description =
    (address = (protocol = tcp)(host = localhost)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orcl)
    )
  )

2.3 修改客户端文件

d:\app\shuhao\instantclient_11_2\network\admin\tnsnames.ora文件内容:

# tnsnames.ora network configuration file: d:\app\shuhao\product.2.0\dbhome_1\network\admin\tnsnames.ora
# generated by oracle configuration tools.
 
oraclr_connection_data =
  (description =
    (address_list =
      (address = (protocol = tcp)(host = localhost)(port = 1521))
    )
    (connect_data =
      (service_name = orcl)
    )
  )
 
listener_orcl =
  (address = (protocol = tcp)(host = localhost)(port = 1521))
 
orcl =
  (description =
    (address = (protocol = tcp)(host = localhost)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orcl)
    )
  )
#新增 @ 标识符，用于区分本地与远程oracle
orcl@192.168.0.164 =
  (description =
    (address = (protocol = tcp)(host = 192.168.0.164)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orcl)
    )
  )

step3 配置oci

不登录plsql，直接按cancel，进入后在tools—>preferences—->connection中配置oci为32位客户端的oci

d:\app\shuhao\instantclient_11_2\oci.dll

step4 环境变量配置

添加前2个系统变量：

1. 变量名：tns_admin

变量值：d:\app\shuhao\instantclient_11_2\network\admin

2. 变量名：nls_lang

变量值：simplified chinese_china.zhs16gbk

3. path中配置加入：

d:\app\shuhao\product\11.2.0\dbhome_1\bin;d:\app\shuhao\instantclient_11_2;

step5 重启电脑（有时候不需要重启）

启动oracle服务与监听，这个就不需要描述了。

step6 登录plsql！！！

当看到database下拉框中有本地的orcl以及远程的orcl@192.168.0.164就算成功了！！！

ok, game over !

到此这篇关于plsql连接本地oracle或远程oracle数据库实现随意切换功能的文章就介绍到这了,更多相关plsql连接本地远程oracle内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！