PLSQL连接本地、远程oracle数据库问题解决

前言

本文主要讲述如何连接本地oracle数据库或者远程oracle数据库，实现随意切换。

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

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

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

step1：复制network文件

安装步骤网上可以搜到！

oracle安装到某路径下，

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

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

粘贴到客户端文件夹D:\app\shuhao\instantclient_11_2下面

====================================分割线=====================================

 

step2：修改配置文件

1. 修改oracle数据库文件

D:\app\shuhao\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora文件内容，添加一个SID_DESC：

1. # listener.ora Network Configuration File: D:\app\shuhao\product\11.2.0\dbhome_1\network\admin\listener.ora

2. # Generated by Oracle configuration tools.

3.  

4. SID_LIST_LISTENER =

5. (SID_LIST =

6. (SID_DESC =

7. (SID_NAME = CLRExtProc)

8. (ORACLE_HOME = D:\app\shuhao\product\11.2.0\dbhome_1)

9. (PROGRAM = extproc)

10. (ENVS = "EXTPROC_DLLS=ONLY:D:\app\shuhao\product\11.2.0\dbhome_1\bin\oraclr11.dll")

11. )

12. #下面的SID_DESC是新增加的内容，注意修改ORACLE_HOME路径

13. (SID_DESC =

14. (SID_NAME = orcl)

15. (ORACLE_HOME = D:\app\shuhao\product\11.2.0\dbhome_1)

16. (GLOBAL_DBNAME = orcl)

17. )

18. )

19.  

20. LISTENER =

21. (DESCRIPTION_LIST =

22. (DESCRIPTION =

23. (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

24. )

25. )

26.  

27. ADR_BASE_LISTENER = D:\app\shuhao

 

====================================分割线=====================================

2. 修改oracle数据库文件

D:\app\shuhao\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora文件内容，源文件没有做改动：

1. # tnsnames.ora Network Configuration File: D:\app\shuhao\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

2. # Generated by Oracle configuration tools.

3.  

4. ORACLR_CONNECTION_DATA =

5. (DESCRIPTION =

6. (ADDRESS_LIST =

7. (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

8. )

9. (CONNECT_DATA =

10. (SERVICE_NAME = orcl)

11. )

12. )

13.  

14. LISTENER_ORCL =

15. (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

16.  

17. ORCL =

18. (DESCRIPTION =

19. (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

20. (CONNECT_DATA =

21. (SERVER = DEDICATED)

22. (SERVICE_NAME = orcl)

23. )

24. )

====================================分割线=====================================

3. 修改客户端文件

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

1. # tnsnames.ora Network Configuration File: D:\app\shuhao\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

2. # Generated by Oracle configuration tools.

3.  

4. ORACLR_CONNECTION_DATA =

5. (DESCRIPTION =

6. (ADDRESS_LIST =

7. (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

8. )

9. (CONNECT_DATA =

10. (SERVICE_NAME = orcl)

11. )

12. )

13.  

14. LISTENER_ORCL =

15. (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

16.  

17. ORCL =

18. (DESCRIPTION =

19. (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

20. (CONNECT_DATA =

21. (SERVER = DEDICATED)

22. (SERVICE_NAME = orcl)

23. )

24. )

25. #新增 @ 标识符，用于区分本地与远程oracle

26. ORCL@192.168.0.164 =

27. (DESCRIPTION =

28. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.164)(PORT = 1521))

29. (CONNECT_DATA =

30. (SERVER = DEDICATED)

31. (SERVICE_NAME = orcl)

32. )

33. )

 

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 !