How to use sqlplus to connect to database in Oracle
How do I use sqlplus to connect to a database in Oracle? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
Mode 1 (native): / as sysdba
In the oracle server, you can log in directly through the operating system authority authentication and use sysdba, as long as you can log in to the server and have this permission.
Oracle@e871d42341c0:~$ iduid=1000 (oracle) gid=1000 (dba) groups=1000 (dba) oracle@e871d42341c0:~$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 08:20:51 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > show userUSER is "SYS" SQL >
Method 2 (native): sqlplus username / password
In addition to sysdba, you can also log in through your user name and password.
Oracle@e871d42341c0:~$ sqlplus system/liumiao123SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 08:21:27 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > show userUSER is "SYSTEM" SQL >
Method 3: through tnsname
Through the tns setting, use the sqlplus username / password @ Oracle instance name to connect when connectivity is guaranteed.
Verify that the tns connection is smooth
Oracle@e871d42341c0:~$ tnsping XETNS Ping Utility for Linux: Version 11.2.0.2.0-Production on 21-OCT-2018 10:32:55Copyright (c) 1997, 2011, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = e871d42341c0) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) OK (0 msec) oracle@e871d42341c0:~$
Confirm that the listening process of oracle starts normally
Oracle@e871d42341c0:~$ ps-ef | grep lsnr | grep-v greporacle 27 1 0 Oct16? 00:00:28 / u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER-inheritoracle@e871d42341c0:~$
Connect
Oracle@e871d42341c0:~$ sqlplus system/liumiao123@XESQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 10:34:04 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > show userUSER is "SYSTEM" SQL >
Mode 4: positioning by IP and port
You can also locate the Oracle instance through IP and port to connect: sqlplus username / password @ / / IP address or hostname: Port number / Oracle instance name
# netstat-tunlp | grep 1521tcp6 00: 1521: * LISTEN-# ip ad | grep 172.17 inet 172.17.0.2 Production on Sun Oct 16 scope global eth0# sqlplus system/abcd1234@//172.17.0.2:1521/XESQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 10:37:31 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL >
Method 5: use nolog and connect to connect
To be exact, there is no essential difference between this method and method 2 / method 3 / method 4, except that the user name / password and the information of the instance name are written differently, as follows:
# sqlplus / nologSQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 11:19:50 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.SQL > connect system/liumiao123Connected.SQL > connect system/liumiao123@XEConnected.SQL > connect system/liumiao123@//172.17.0.2:1521/XEConnected.SQL > show userUSER is "SYSTEM" SQL > after reading the above, do you know how to use sqlplus to connect to the database in Oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
