SQLPlus Connect
To connect a database, we usually use sqlplus, a text-based connection tool provided by Oracle to facilitate clients to connect to a running database. Since sqlplus needs to know where it should go, you should additionally provide a correct connection string.
To enable non-oracle to run sqlplus on Linux database server, you need to configure it for the user, although it's easy to setup.
SQLPlus Connection String
The format of connection string we should provide to sqlplus is usually consist of 3 components.
The first and second components are username and password, which form the pair of credential used to authenticate the connection by the database. The third one is the destination, we usually use a local naming alias.
Connect with TNSNAMES
Local naming is a connection alias system, which is managed by a local configuration file called tnsnames.ora to organize our complex connection information into a short, simple and easy-to-remember alias.
Conventionally, we call local naming connection, the alias management system as TNSNAME for short. Which means, a TNSNAME represents the destination where you want to connect to.
Connect without TNSNAMES
However, you can have a choice to sqlplus without using TNSNAMES to connect to a database, it's also practical to check if a database can be connected in a quick way, you may have a look.
Next, let's see how we connect and disconnect by SQLPlus.
SQLPlus Connect
There're several ways to connect to a database in sqlplus.
Connect at sqlplus
We can append the connection string to command sqlplus to connect to a database.
C:\Users\scott>sqlplus hr/password@orclpdb
...
SQL> show user
USER is "HR"
Or without specifying password.
C:\Users\scott>sqlplus hr@orclpdb
...
Enter password:
As you can see, sqlplus prompts you for password.
Connect in sqlplus
We can connect to a database after we enter the interactive mode of sqlplus. You may use either command CONNECT or CONN to initiate a connection, they are synonyms.
C:\Users\scott>sqlplus /nolog
...
SQL> conn hr/password@orclpdb
Connected.
Or without specifying password.
SQL> conn hr@orclpdb
Enter password:
As we expect, sqlplus prompts you for password.
More precisely, sqlplus /nolog means that you just want to enter the interactive mode of sqlplus without connecting any database.
Connect after Connected
If you have connected to a database, you may connect to another one.
SQL> conn hr/password@orclpdb
Connected.
SQL> conn sh/password@orclpdb
Connected.
In such manner, sqlplus implicitly disconnect the previous connection, then handle the new connection request in order.
SQLPlus Disconnect
There're several ways to disconnect from a database in sqlplus.
Disconnect without Leaving SQLPlus
To explicitly disconnect a session without leaving sqlplus interactive mode, you can issue DISCONNECT or DISC, they are synonyms.
SQL> disconnect
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
As you can see, we're still in the interactive mode of sqlplus. We may initiate another new connection any time.
Disconnect and Leaving SQLPlus
If you are no longer to use sqlplus, you can issue EXIT or QUIT to disconnect a session and leave the program.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
C:\Users\scott>
As you can see, we exited the program.
Disconnect Implicitly
As we have talked about earlier, if you have connected to a database, you may connect to another one without saying anything.
SQL> conn hr/password@orclpdb
Connected.
SQL> conn sh/password@orclpdb
Connected.
Initiating another connect in sqlplus will disconnect the current session from the database implicitly and start another new session in the same program.
Interrupt SQLPlus
If you have been stuck in some operation, you may press one or more Ctrl + c to interrupt the program forcibly, it will terminate the connection unilaterally because sqlplus won't have a chance to notify the database.
SQL> exec remote_update();
^C
C:\Users\scott>
Since the connection was terminated by an interruption, the database has no idea what happen about the session. After a while, PMON will reclaim the server process and roll back any uncommitted transactions on it.
For sessions, whether transactions will be implicitly committed or not while disconnecting from the database is big issue. It depends on how you disconnect the session. We have talked about it in How to Set Autocommit ON and OFF.