Connect To Oracle Database

 Summary: in this tutorial, you will learn how to connect to Oracle database server using SQL*plus and SQL developer tools.

Connect to Oracle Database Server using SQL*Plus

SQL*Plus is an interactive query tool installed automatically when you install Oracle Database Server or Client. SQL*Plus has a command-line interface that allows you to connect to the Oracle Database server and execute statements interactively.

If you have worked with MySQL or PostgreSQL, SQL*plus is similar to the mysql program in MySQL or psql in PostgreSQL.

To launch the SQL*Plus program, from the Linux or Windows terminal, you enter the sqlplus command:

sqlplusCode language: SQL (Structured Query Language) (sql)

You can also find the SQL*Plus program in the Program folder of the Start Menu on Windows as shown below:

When you launch the SQLPlus icon, it will prompt for a username and password. Enter the username and password that you chose during the installation of the Oracle Database Server. If you don’t know which account to use, ask your Database Administrator.

Suppose you want to connect to the local Oracle Database Server using the sys account, you enter the following information:

Enter user-name: sys as sysdba
Enter password:Code language: SQL (Structured Query Language) (sql)

After pressing enter, you should see a message followed by the SQL> command line as follows:

SQL>_Code language: SQL (Structured Query Language) (sql)

It means that you have connected to the Oracle Database Server.

In Oracle 12c, when you connect to the Database Server, the default database is the ROOT container database with the name CDB$ROOT. To display the database name, you use the SHOW command:

SQL> SHOW con_name;

CON_NAME
------------------------------
CDB$ROOT
Code language: SQL (Structured Query Language) (sql)

If you want to switch to a pluggable database, use the ALTER SESSION statement to set the current database to the pluggable database e.g., PDBORDL as follows:

SQL> ALTER SESSION SET CONTAINER = PDBORCL;

Session altered.Code language: SQL (Structured Query Language) (sql)

Now, you connect to the PDBORCL database.

To disconnect a user from the Oracle Database Server, you use the EXIT command:

SQL> EXITCode language: SQL (Structured Query Language) (sql)

To connect the OT user to the sample database located in the PDBORCL pluggable database, you enter the following command:

>sqlplus OT@PDBORCLCode language: SQL (Structured Query Language) (sql)

SQL*Plus prompts for the password of the OT user. Enter the password and you will be connected to the PDBORCL database in the Oracle Database Server.

Connect to Oracle Database Server using SQL Developer

SQL Developer is a free GUI tool for working with SQL in Oracle Databases. Like the SQL*Plus program, SQL Developer is installed automatically when you install the Oracle Database Server or Client.

To launch the SQL Developer program, you click the SQL Developer icon in the Oracle Program folder in Start Menu as shown in the following picture:

The following shows the SQL Developer program:

To create a new database connection, (1) first, click the New button or press Ctrl-N, and then (2) choose Database Connection option and click the OK button.

The following New / Select Database Connection dialog will display:

In this dialog, you need to enter the following information:

First, enter the following information:

• A connection name. It can be any name you like, we used OT as shown in the dialog.
• Username and its password, which is  ot/Orcl1234 in this case.
• Save the password by checking the Save Password checkbox.

Second, choose TNS as the connection type.

Third, the Network Alias list will be displayed after you choose the TNS connection type. Just select the PDBORCL as the network alias. By doing this, you explicitly specify the pluggable database PDBORCL to which you want to connect.

Fourth, click the Test button to test the database connection. If you see status: Success message, you are ready to connect to the Oracle Database Server.

To save time re-entering all the connection parameters again, click the Save button. The connection will be saved and displayed on the left side of the dialog.  Whenever you want to reconnect, you just need to click the saved connection, all the parameters will be filled out automatically.

Now, you can click the Connect button to connect to the Oracle Database Server.

Double click the Connections > OT > Tables, and you will see all tables in the OT sample database.

In this tutorial, you have learned how to connect to the Oracle Database Server using SQL*Plus and SQL Developer.