Connect to oracle database

In this Oracle tutorial, we will learn how to connect to oracle databases 19c & 21c. Also, we will cover these topics in this tutorial.

• Connect to oracle database command line
• Connect to oracle database in Linux
• Connect to oracle database from Docker container
• Connect to oracle database using SQL developer
• Connect to oracle database using python
• Connect to oracle database using python pyodbc
• Connect to Oracle to oracle database in python

Connect to oracle database command line

Using sqlplus, we can connect to the oracle database through the command line on the windows operating system. After installing the oracle database search for SQLPLUS on the windows search.

The below image shows SQL Plus on the windows 10 operations system.

Other than this, you can access sqlplus using the command prompt. Start the command prompt and type sqlplus. SQL Plus environment will be activated and now provide the username and password of the account through which you want to connect to the oracle database.

The default user is SYS, at first, it won’t ask for a password but it is recommended to set the password once you are logged in. The below command line shows how to connect to the oracle database using sys user.

-- On SQLPLUS
sys as sysdba

-- On Command Prompt
sqlplus sys as sysdba

Once you are connected successfully you can run all the Oracle & PL/SQL queries on the windows operating system.

Below is the syntax to connect to the oracle database through the command line:

CONNECT username/password@connect_identifier

Syntax Description:

• Username: provide username for the account or database you want to connect with.
• Password: provide the password for the entered username.
• Connect_Identifier: provide connection information like localhost, Ipaddress, pdb or orcl information.

Also, check: How to create a database in Oracle

Connect to oracle database in Linux

You can connect to the oracle database in Linux through the terminal by activating the SQL Plus environment. Start terminal and type sqlplus it will prompt for username and password. Provide the username and password of the database you want to connect with.

If you are starting oracle immediately after installing it on Linux operating system in that case you won’t have any username and password so at that time you can log in using sys user.

Sys user is the system administrator account that can be logged in without any password. But once logged in set the password for it. The below script shows how to connect to the oracle database in Linux.

sqlplus sys as sysdba

In case you want to connect to some other user then put in the value in the below syntax to connect to the oracle database of your choice in the Linux operating system.

CONNECT username/password@connect_identifier

Syntax Description:

• Username: provide username for the account or database you want to connect with.
• Password: provide the password for the entered username.
• Connect_Identifier: provide connection information like localhost, Ipaddress, pdb or orcl information.

Read: How to Check Oracle Database Version

Connect to oracle database from Docker container

Docker is a containerization tool using which lightweight operating systems and other software can be installed and used quickly.

Once the task is completed developer can remove that os or software easily from the system. Docker has a huge community and software support so most of the software and operating systems are available on the docker hub.

In this section, we will show you how to connect to the oracle database using the docker container. We assume that you have installed docker & set up an oracle image on it.

Read: How to Install Oracle on Docker

Steps to connect to oracle database from Docker container:

1) Once you have pulled the oracle image from the docker hub use the below command to run it.

• -d will run the image in detached mode
• -p is for port which is 1521
• –name is the alias provided to the image. In this case name is ‘oracle’.

docker run -d -p 1521:1521 --name oracle store/oracle/database enterprise:12.2.0.1

2) If the oracle is not started with the above command you can manually start or stop it using the below commands.

- start oracle
docker start oracle

- stop oracle
docker stop oracle

3) Start the SQL developer tool and click on the green plus icon on the top left to create a new connection.

Below are the connection details for both Container Database (CDB) and Pluggable Database (PDB). Select the SID radio button to fill CDB details and to connect with PDB select the service name radio button on the SQL developer tool.

Container Database (CDB)
Username: SYS
Role: SYSDBA
Password: Oradoc_db1
Host: localhost
SID: ORCLCDB

Pluggable Database (PDB)
Username: SYS
Role: SYSDBA
Password: Oradoc_db1
Host: localhost
Service Name: ORCLPDB1.localdomain

The below image shows how to connect to the oracle database from the docker container. The information filled in the form is mentioned above. Click on the connect button and oracle will be connected through the docker container.

Note: If you have oracle installed on your system directly then you may get a port already occupied error as mentioned below.

Error response from daemon: Ports are not available: listen tcp 0.0.0.0:1521: bind: Only one usage of each socket address (protocol/network address/port) is normally permitted.
Error: failed to start containers: oracle

To fix this error, go to services and stop OracleOraDB19Home1MTSRecoveryService and OracleOraDB19Home1TNSListener.

Read: Connect Excel to Oracle Database

Connect to oracle database using SQL developer

Oracle SQL developer tool is capable of running huge SQL queries that too with graphical user interface support. This section is all about how to connect the Oracle databases using the SQL developer tool.

Follow these steps to connect to Oracle database using SQL developer tool:

• Click the green plus icon on the top left corner of the SQL developer tool.
• Provide the username and password for the user through which you want to connect to the database.
• Select the role of that user, incase no role is defined then leave it to default.
• Provide the host information. Host information could be localhost or IP address of network.
• If you want to connect through container database (CD) then select SID radiobutton and provide the CD name. By default it is orcl.
• To connect through Pluggable database (pdb) select the service name radiobutton and provide the Pluggable database name.
• Test the connection if success then go ahead and connect to the Oracle database using SQL developer tool.

In the below image we are connecting through a sys user whose role is sysdba and we connecting to a pluggable database (PDB).

If still in doubt watch our video on the SQL developer tool where we have demonstrated the above steps.

Connect to oracle database using python

Oracle is a powerful database that can be used for backend management with other powerful languages like python and java. We have created a dedicated blog on how to connect to oracle database using python. In that blog, we have covered topics like:

• Connect to oracle database using python pyodbc
• Connect to Oracle to oracle database in python

You may also like to read the following Oracle tutorial.

• Number Datatype in Oracle
• Oracle Add Row Number
• Oracle check database status
• Oracle get database name
• Oracle Add a Column to Table
• Oracle Create User – Detailed Guide
• Convert rows into columns in Oracle

In this tutorial, we have learned how to connect to oracle databases 19c & 21c. Also, we have covered these topics.

• Connect to oracle database command line
• Connect to oracle database in Linux
• Connect to oracle database from Docker container
• Connect to oracle database using SQL developer
• Connect to oracle database using python
• Connect to oracle database using python pyodbc
• Connect to Oracle to oracle database in python