So you have installed the Oracle Database Express Edition on your machine and now you want to start working on it. The first thing that you will be looking for is how to connect with the Oracle Database Express Edition. In this tutorial, I will show you how to connect with Oracle xe using tools
- SQLPLUS, and
- SQL Developer
Both these tools are industry-standard compliant and free to download and use. Let’s start with the first one – SQLPLUS
Connect With Oracle Express Edition Using SQLPLUS
SQLPLUS is a command-line utility that comes free and is already installed with the Oracle XE. You can access it through the Windows command prompt or Linux terminal. Here are the steps of doing that
For the demonstration, I will be establishing a connection with the database using an SYS user.
Step 1: Open up the command prompt or Linux terminal
Step 2: Access the SQLPLUS CLI like this
C> SQLPLUS / as sysdba
On successful execution of the above statement, you will be connected with the sys user of Oracle Database express edition.
Since sys is an external user thus a forward slash will eradicate the need to write the username and password. You can learn more about External users here.
Connect With Oracle Express Edition Using SQL Developer
SQL Developer is a powerful graphic user interface created by Oracle itself. Similar to Oracle SQLPLUS, SQL Developer is also available for FREE to download and use.
You can download SQL developer from the link given below
https://www.oracle.com/tools/downloads/sqldev-downloads.html
Graphical Guide for Database connection.
Here is a graphical guide on how to make database connections using SQL Developer
In case you don’t know the hostname and port number registered with your Oracle Database express edition, you can find out this information from LISTENER.ORA file. I have demonstrated the process of how you can do that in the corresponding video. ⬇️
Connect With Username & Password
So far I have demonstrated how to connect with the SYS user. Which basically doesn’t need you to write a username and password. But what if you want to connect with a normal user which requires you to specify a username and password. Now you will learn how to do that –
There are two techniques that we will be using for that, these are-
- Using TNS service, and
- Using Connect String.
Using TNS Service
First of all, if you don’t know what is a TNS service and how to create one then watch this tutorial here.
Here are the steps for connecting with Oracle Database XE using Username and Password
For the demonstration the username that I will be using here is HR. The password for this user is also HR and the Name of the TNS service is XEPDB1. XEPDB1 is also the name of the pluggable database that has this HR user. So, let’s use this information to connect with the Database
Step1: Open up the command prompt or Linux terminal
Step 2: Run the SQLPLUS command and write
C> SQLPLUS hr/hr@XEPDB1
It is a simple command which follows this format
SQLPLUS username/password@TNS_Service_Name
On successful execution of the above statement, you will be connected with the HR user of Oracle Database express edition.
Using Connect String
If you don’t know the TNS service name but you know the hostname, port number and the pluggable database name which has your user then you can use this information to connect with the database.
Here is the simple format to connect with the Oracle Database Express Edition using Connect String.
SQLPLUS username/password@hostName:portNumber/Service_name
In most cases, the service name is the same as the name of the pluggable or container database that has the user in it.
If we follow the above format to connect with the HR user then the string will look something like this
C> SQLPLUS hr/hr@localhost:1521/XEPDB1
In my case the service name is XEPDB1, it could be different in your case. So be careful. Also, you can find out the host name and port number of your database from LISTENER.ORA file.
That is how you connect with Oracle Database Express Edition. Hope you will find this tutorial helpful.
Thanks and have a great day.