作者专注于Java、架构、Linux、小程序、爬虫、自动化等技术。 工作期间含泪整理出一些资料,微信搜索【程序员高手之路】,回复 【java】【黑客】【爬虫】【小程序】【面试】等关键字免费获取资料。 


目录

​前言​

​step1 复制network文件​

​step2 修改配置文件​

​2.1  修改oracle数据库文件​

​2.2  修改oracle数据库文件​

​2.3  修改客户端文件​

​step3 配置OCI​

​step4 环境变量配置​

​step5 重启电脑(有时候不需要重启)​

​step6 登录plsql!!!​


前言

先赞后看,此生必赚!

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

有木有想说句 ​TMD​ 平复一下心情?

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

注:也可以用plsql连接远程数据库(只要有oracle的​network\admin\tnsnames.ora​就行)。

首先下载64oracle以及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下面,如下图所示:

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


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\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:\app\shuhao\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\shuhao\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
#下面的SID_DESC是新增加的内容,注意修改ORACLE_HOME路径
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = D:\app\shuhao\product\11.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\11.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\11.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

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

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就算成功了!!!

PLSQL连接本地oracle或远程oracle数据库,实现随意切换_连接本地oracle_03

OK, GAME OVER !

更多内容,请关注公众号:​程序员高手之路

在公众号回复:​Oracle资源​   即可免费获取Oracle视频教程!

PLSQL连接本地oracle或远程oracle数据库,实现随意切换_数据库_04

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