颠末过程sqlplus可以或许或许衔接数据库根据用户权限停止数据或许设定操纵,这篇文章介绍一下常用的衔接办法。

环境准备

应用Oracle的精简版创建docker办法的demo环境。

办法1(本机): / as sysdba

在oracle效劳器,可以或许或许间接颠末过程操纵体系权限认证,应用sysdba办法登岸,条件是你可以或许或许登入效劳器,而且拥有此权限。

  1. oracle@e871d42341c0:~$ id
  2. uid=1000(oracle) gid=1000(dba) groups=1000(dba)
  3. oracle@e871d42341c0:~$ sqlplus / as sysdba
  4. SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 08:20:51 2018
  5. Copyright (c) 1982, 2011, Oracle. All rights reserved.
  6. Connected to:
  7. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  8. SQL> show user
  9. USER is "SYS"
  10. SQL>

办法2(本机): sqlplus 用户名/密码

在本机除了sysdba,还可以或许颠末过程用户名和密码的办法登岸进来

  1. oracle@e871d42341c0:~$ sqlplus system/liumiao123
  2. SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 08:21:27 2018
  3. Copyright (c) 1982, 2011, Oracle. All rights reserved.
  4. Connected to:
  5. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  6. SQL> show user
  7. USER is "SYSTEM"
  8. SQL>

办法3: 颠末过程tnsname办法

颠末过程tns设定,包管联通性的环境下应用 sqlplus 用户名/密码@Oracle实例名 的办法停止衔接。

确认tns衔接通行

  1. oracle@e871d42341c0:~$ tnsping XE
  2. TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 21-OCT-2018 10:32:55
  3. Copyright (c) 1997, 2011, Oracle. All rights reserved.
  4. Used parameter files:
  5. Used TNSNAMES adapter to resolve the alias
  6. Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = e871d42341c0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
  7. OK (0 msec)
  8. oracle@e871d42341c0:~$

确认oracle的监听过程正常启动

  1. oracle@e871d42341c0:~$ ps -ef |grep lsnr |grep -v grep
  2. oracle   27   1 0 Oct16 ?    00:00:28 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inherit
  3. oracle@e871d42341c0:~$

衔接

  1. oracle@e871d42341c0:~$ sqlplus system/liumiao123@XE
  2. SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 10:34:04 2018
  3. Copyright (c) 1982, 2011, Oracle. All rights reserved.
  4. Connected to:
  5. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  6. SQL> show user
  7. USER is "SYSTEM"
  8. SQL>

办法4: IP和port的办法定位

还可颠末过程IP和port的办法定位Oracle实例停止衔接:sqlplus 用户名/密码@//IP地址或许hostname:端口号/Oracle实例名

  1. # netstat -tunlp |grep 1521
  2. tcp6    0   0 :::1521         :::*          LISTEN   -       
  3. # ip ad |grep 172.17
  4.   inet 172.17.0.2/16 scope global eth0
  5. # sqlplus system/abcd1234@//172.17.0.2:1521/XE
  6. SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 10:37:31 2018
  7. Copyright (c) 1982, 2011, Oracle. All rights reserved.
  8. Connected to:
  9. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  10. SQL>

办法5: 应用nolog 和 connect实现衔接

精确的来说,这种办法和办法2/办法3/办法4没有本质差别,无非便是用户名/密码和实例名的信息的写法分歧而已,详细如下:

  1. # sqlplus /nolog
  2. SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 11:19:50 2018
  3. Copyright (c) 1982, 2011, Oracle. All rights reserved.
  4. SQL> connect system/liumiao123
  5. Connected.
  6. SQL> connect system/liumiao123@XE
  7. Connected.
  8. SQL> connect system/liumiao123@//172.17.0.2:1521/XE
  9. Connected.
  10. SQL> show user
  11. USER is "SYSTEM"
  12. SQL>

总结

以上便是oracle数据库颠末过程sqlplus衔接的几种办法的全体内容了,盼望本文的内容对大家的学习或许工作具有一定的参考学习价值,