PLSQL连接本地oracle或远程oracle数据库

本文以oracle11G为列，演示PLSQL连接oracle11G(64位)服务端

一、Oracle11G安装包如下：

oracle11G服务端安装包链接：https://pan.baidu.com/s/1e7Te4kHYSo0ytzJNx0QfLA 提取码：tn63 oracle11G客户端安装包链接：https://pan.baidu.com/s/149zYoKDkRjbhiz8y1pqT1A 提取码：ziim oracle11G 初始化安装包链接：https://pan.baidu.com/s/1vdqtZKnxTJGCunsC-GBNkQ 提取码：yimn PLSQL图形界面工具链接：https://pan.baidu.com/s/1SCdJOZAFJ5wZftIN7uqJ8Q 提取码：gcf8

二、安装步骤：

安装步骤网上搜索，本文不再讲述。

安装路径请自行更改路径

1、 我的PLSQL安装目录如下：D:\oracle\PLSQL Developer 8.02，如下图：

2、我的客户端安装目录如下：D:\oracle\OracleClient 把初始化安装包放到客户端的D:\oracle\OracleClient\product目录下，如下图： 我的服务端安装目录如下：D:\oracle\OraclePro

三、PLSQL连接oracle11G(64位)服务端步骤

1、把 oracle服务端目录D:\oracle\OraclePro\product\11.2.0\dbhome_1下的NETWORK文件夹复制，粘贴到客户端文件夹目录D:\oracle\OracleClient\product\instantclient_11_1下2、修改配置文件

• 修改oracle服务端的数据库文件，把D:\oracle\OraclePro\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora文件内容，添加一个SID_DESC：

# listener.ora Network Configuration File: D:\oracle\OraclePro\product\11.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:\oracle\OraclePro\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\OraclePro\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LAPTOP-QB2KUOCC)(PORT = 1521))
    )
  )
#下面的SID_DESC是新增加的内容，注意修改ORACLE_HOME路径
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = D:\oracle\OraclePro\product\11.2.0\dbhome_1)
      (GLOBAL_DBNAME = orcl)
    )
  )
ADR_BASE_LISTENER = D:\oracle\OraclePro

• 修改oracle服务端的数据库文件，把D:\oracle\OraclePro\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora文件内容，如果文件中已存在不用修改。

# tnsnames.ora Network Configuration File: D:\oracle\OraclePro\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(HOST = localhost)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
	  (SERVICE_NAME = orcl)
    )
  )
  
#下面的LISTENER_ORCL是新增加的内容，如果文件中已存在不用修改
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  
#下面的ORCL是新增加的内容，注意修改HOST的地址和SERVICE_NAME的名称，如果文件中已存在不用修改
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

• 修改oracle客户端文件，D:\oracle\OracleClient\product\instantclient_11_1\NETWORK\ADMIN\tnsnames.ora文件内容:

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


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
	  (SERVICE_NAME = orcl)
    )
  )
#新增本人用的测试服务器中的oracle
lycs54 =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 10.194.1.54)(PORT = 1521))
       )
       (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = lycs)
       )
   )
#新增连接本地oracle
ORCL =
	  (DESCRIPTION =
	      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
		  (CONNECT_DATA =
		        (SERVER = DEDICATED)
			    (SERVICE_NAME = orcl)
		   )
	  )
	  
#新增 @ 标识符，用于区分本地与远程oracle
TEST@192.168.1.110 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.110)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

• 配置oracle主目录库和OCI库

不登录plsql，直接按Cancel，进入后在Tools—>Preferences---->Connection中配置 主目录库路径为 D:\oracle\OracleClient\product\instantclient_11_1 如下图： OCI库的路径为 D:\oracle\OracleClient\product\instantclient_11_1\oci.dll 如下图：

• 环境变量配置（有时候不需要重启） 变量名：TNS_ADMIN 变量值：D:\oracle\OracleClient\product\instantclient_11_1\NETWORK\ADMIN

  变量名：NLS_LANG 变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Path中配置加入：D:\oracle\OraclePro\product\11.2.0\dbhome_1\BIN;D:\oracle\OracleClient\product\instantclient_11_1;

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

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

• 登录plsql

当看到Database下拉框中有本地的ORCL以及远程的TEST@192.168.1.110就算成功了！！！