本地使用Plsql连接远程服务器上oracle！

男娘i 2022-08-01 06:02 278阅读 0赞

前提：先关掉客户机和服务器防火墙吧！

1.安装oracle到服务器  
2.下载instantclient，并安装  
3.拷贝oracle的home下network文件夹到 instanclient下，只保留admin里面的tnsnames.ora文件  
4.更改tnsnames.ora文件，如下：  
ORCL =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = orcl)  
    )  
  )  
5.配置环境变量：  
TNS\_ADMIN  ---D:\\instantclient\_11\_2\\NETWORK\\ADMIN; (该路径为instancclient下tnsnames.ora的路径)  
配置plsql编码方式与服务端一致  
环境变量  
NLS\_LANG-----AMERICAN\_AMERICA.UTF8  
NLS\_LANG-----SIMPLIFIED CHINESE\_CHINA.ZHS16GBK  
6.打开plsqldev,选择工具，首选项，更改  
oracle主目录名：D:\\instantclient\_11\_2  
oci库：D:\\instantclient\_11\_2\\oci.dll--instanceclient下的oci.dll文件  
7.远程服务器里oracle home下，D:\\oracleDB\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN  
的tnsnames.ora和listener.ora里面的 host的ip：  
如下：  
\------------------------tnsnames.ora-----------------------  
\# tnsnames.ora Network Configuration File: D:\\oracledata\\product\\11.2.0\\dbhome\_1\\network\\admin\\tnsnames.ora  
\# Generated by Oracle configuration tools.  
  
  
LISTENER\_ORCL =  
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(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 = 192.168.1.200)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = orcl)  
    )  
  )  
  
  
\----------------------listener.ora---注意添加该内容------------------------  
\# listener.ora Network Configuration File: D:\\oracleDB\\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:\\oracleDB\\product\\11.2.0\\dbhome\_1)  
      (PROGRAM = extproc)  
      (ENVS = "EXTPROC\_DLLS=ONLY:D:\\oracleDB\\product\\11.2.0\\dbhome\_1\\bin\\oraclr11.dll")  
    )  
  )  
\-------------添加的内容,否则容易报字符串错误-----------  
(SID\_DESC =  
    (GLOBAL\_DBNAME = ORCL)  
    (ORACLE\_HOME = D:\\oracleDB\\product\\11.2.0\\dbhome\_1)  
    (SID\_NAME = ORCL)  
    )  
\-------------------------------  
LISTENER =  
  (DESCRIPTION\_LIST =  
    (DESCRIPTION =  
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))  
    )  
  )  
  
  
ADR\_BASE\_LISTENER = D:\\app\\Administrator  
\-------------------------------------------------  
8.重启服务器的oracle的service服务和listener服务，客户机进行登录即可