连接Linux Oracle 数据库

一、oracle 用户登录 Linux 操作配置监听

1.1 查询监听状态

 lsnrctl status
 [oracle@localhost ~]$ lsnrctl status
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-AUG-2021 16:30:55
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Linux Error: 111: Connection refused
 [oracle@localhost ~]$

监听没打开，则打开监听服务：lsnrctl start​

 [oracle@localhost ~]$ lsnrctl start
 
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-AUG-2021 16:46:45
 
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
 Starting /data/u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
 
 TNSLSNR for Linux: Version 11.2.0.1.0 - Production
 Log messages written to /data/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
 
 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
 Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
 Start Date                17-AUG-2021 16:46:45
 Uptime                    0 days 0 hr. 0 min. 0 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Listener Log File         /data/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
 Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
 The listener supports no services
 The command completed successfully
 [oracle@localhost ~]$

1.2 以 sysdba 身份连接数据库

 sqlplus sys/sys as sysdba
 [oracle@localhost ~]$ sqlplus sys/sys as sysdba
 SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 17 21:46:54 2021
 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 SQL>

**创建用户跟密码：**​create user 自定义用户名 identified by 自定义密码;​//注意分号不能少 授权用户登录权限：grant create session to 上面创建的用户;​ 授权用户连接、创建视图等权限：grant connect,create view,resource to 上面创建的用户;​ 授权用户使用表空间的权限：grant unlimited tablespace to 上面创建的用户;​

 SQL> create user cydtest identified by 12345678;
 User created.
 SQL> grant create session to cydtest;
 Grant succeeded.
 SQL> grant connect,create view,resource to cydtest;
 Grant succeeded.
 SQL> grant unlimited tablespace to cydtest;
 Grant succeeded.
 SQL>

1.3 查看 Linux 监听配置

 vim /data/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
 # listener.ora Network Configuration File: /data/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
 # Generated by Oracle configuration tools.
 
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     )
   )
 
 ADR_BASE_LISTENER = /data/u01/app/oracle
 ~                                                                           
 ~   
 vim /data/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
 # tnsnames.ora Network Configuration File: /data/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
 # Generated by Oracle configuration tools.
 
 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)
     )
   )
 ~                                                                           
 ~

二、Windows 配置

2.1 从 Oracle 官网下载两个文件：

instantclient-basic-windows.x64-11.2.0.4.0.zip​ instantclient-sqlplus-windows.x64-11.2.0.4.0.zip​ 解压到当前文件夹

2.2 修改本地 tnsnames.ora

从 Linux 中 Oracle 安装目录拷贝 tnsnames.ora 到 windows 本地，放置到解压好的 instantclient_11_2 目录中，打开修改 HOST：

 # tnsnames.ora Network Configuration File: /data/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
 # Generated by Oracle configuration tools.
 
 LISTENER_ORCL =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.3)(PORT = 1521))
 
 ORCL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.3)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orcl)
     )
   )

三、PL/SQL 连接

oracle 用户登录 Linux 操作配置监听

参考前文配置 Linux 监听和本地 tnsnames.ora

配置 PL/SQL

1. **下载 Oracle 客户端 instant-client32 位(因为我这边 plsql 是 32 位的)，**​instantclient-basic-nt-11.2.0.4.0.zip
2. 下载后解压，放入 d 盘，添加系统参数 ORACLE_HOME

第一次打开，不登录，配置 Tools-Preferences-Oracle-connection

• Oracle Home：D:\instantclient_11_2x32
• OCI library 修改到客户端绝对路径下 oci.dll 文件，例：D:\instantclient_11_2x32\oci.dll

配置好系统参数 ORACLE_HOME 后会产生连接方式

Oracle Logon 界面

Username： Password：** Database：IP 地址:端口/数据库名 Connect as Normal**

‍