In this tutorial, we will learn how to create a new user and connection in SQL developer. We will also learn how to unlock HR account and understand more about HR schema.
This article is divided in 3 parts:
- Create a New Connection for SYS user
- Create a New Connection for HR User
- Create a New User and Connection in SQL developer
Create a new Database connection for SYS user
This is the first thing that we need to do after installing SQL developer.
Once we have installed Oracle database and SQL developer on our system, we need to create a connection using SQL developer to connect with Oracle database. We can use both SQL developer and Command Prompt to interact with the database.
When we install Oracle database, it comes with some predefined users like STS, SCOTT, SYSTEM, HR etc. SYS user is the one which has all the administrative privileges. We only login to the database using this user when we need to perform administrative tasks.
Since, SYS and SYSTEM users are already created when we install Oracle Database, we just need to create a connection in SQL developer to connect with the database.
Step 1: Open the New Connection Window
Click on the ‘Green Plus’ Sign under Connections on the left to open New Connection Window. This screen opens up.
Here,
- Username is SYS and Password is what you gave at the time of Oracle Database installation. In our case,
- Name – System ( You can give any name, but we give it as System for quick reference )
- username – SYS
- password -system
- Role – SYSDBA
- Connection Type – Basic
Step 2: Check hostname, port and SID in tsnames.ora file
For hostname, port and SID you can once check the tsnames.ora file in the Oracle Installation Directory.
The path of this file on my machine is >>
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tsnames.ora
You can also search for this in your Oracle Installation Directory.
Open this file in Notepad++ or any other text editor and this will give you the hostname, port and SID. Use this information while creating any new user or connection in SQL developer. In most cases it’s default, but in some cases the SID might be different
After this click on Test and then Save to create a new database connection for SYS user in SQL developer.
Create a New Database connection for HR User
HR is other predefined user which comes HR schema. HR schema consists of some database tables which we use for oracle training. These tables are generally used for learning Oracle and we perform all SQL queries on these practice tables.
Just like SYS user, HR user is also automatically created when we install Oracle Database. We just need to create a new connection for this HR user in SQL developer.
Step 1: Open the new connection window in SQL developer
Just like before, click on the ‘Green Plus Sign’ under connections to open the New connection window.
Enter the following information:
- Name- hr
- username -hr
- password -hr
- Role – Default
- Connection Type- Basic
Hostname, Port and SID is the same that we used while creating the SYS user connection. You can also check it in tsnames.ora file.
As you can see, we received the ORA-28000 Error saying the account is locked. This happens because sometimes we forget to unlock these accounts at the time of Oracle Database Installation. We can unlock this account by writing a simple 2 line SQL query.
Step 2: Unlock the HR account in SQL Developer
For this, we use the SQL command Line Tool. Just search for SQL command Line tool in your Start Menu as this tool is also installed when we install the Oracle Database.
SQL Developer is a GUI ( Graphical User Interface) tool which allows us to work with the database using just our mouse and we get to see and edit the data in tables as we do in Excel. But SQL Command Line Tool is just used for writing queries and does not offer any graphical interface to interact with data in the database.
Let’s use SQL command Line tool to write this 2 queries, one after other
connect system/system as sysdba;
alter user hr identified by hr account unlock;
The first line is used for connecting with database using system username and password.
The second line is used for altering the hr user account and instructing Oracle to Unlock it.
User is altered.
Step 3: Create HR connection in SQL Developer
Repeat Step 1 and this time as you can see, it shows success.
Click on Test > Save and then login to this account using
- username- hr
- password -hr
HR Schema
All relational objects like Tables, Views, Functions etc. within a database are owned by a specific user. These objects together are referred to as Schema.
As we already saw, SYS, SCOTT, SYSTEM, HR are some of the users which are created at the time of Oracle Database installation. Each user has its own schema or relational objects associated with it.
HR schema consists of 7 tables and a view and procedure. These are practice tables with dummy data which reflect data used in a real organization.
We use these table to perform operations and manipulate data within these table as per our requirements and to learn Oracle SQL.
Create a New User and Connection
SYS and HR were the users which come preinstalled. Let’s see how to create a brand new user using SQL developer.
Step 1: Login to SYS account and Create a New User
For creating a new user, we need to login to the SYS account using username and password.
Here, we have the option of simply writing a query to create a new user or to use SQL Developer interface to create new user.
Step 2: Open the SYS User schema and go to > Other Users > Create User
Step 3: Give username and password
Give any name you wish, but make sure it does not conflict with the name of any user that comes preinstalled with Oracle.
- Default Tablespace – Users
- Temporary Tablespace – Temp
Step 4 : Grant Privileges
Grant Connect, DBA and Resource Privileges to this new user.
Step 5 : Grant Quota for Tablespace
Users > Unlimited
Click Apply and this new user has been created.
Step 6: Create a New Connection for this New User
Alternate Method to Create New User
We can also create new user and connection using few simple queries.
Step 1:
Let’s directly create the user by using SQL queries by logging into the SYS user account.
CREATE USER ‘obama’ identified by ‘obama’;
Step 2:
Type this query to check, if the account status. If its locked, we may get error. Replace the username with username that you have given.
SELECT * FROM dba_users WHERE username =’obama’;
Step 3 :
Create new connection by clicking on ‘green plus sign’ in SQL developer.
As you can see, we are getting the ORA-01045 error as we have not granted privileges to user.
Step 4:
Let’s grant all privileges to new user:
grant privileges to obama;
Finally, We have created the new user and connection.
Let’s check if everything is working fine by writing one simple query after logging into new user account we just created:
SELECT 1+1 FROM dual;
Everything is working fine.
In this tutorial, we learned how to create a new user and connection in SQL developer.