How to Unlock User or Schema In Oracle Database Using ALTER USER Statement

By Manish Sharma

Unlock User or Schema In Oracle Database Using ALTER USER statement.

Users that are also known as schemas are required to work with your database. They let you connect with your database and access the stored data.

Types of Schema/Users In oracle Database

There are two types of Schemas in oracle database.
  1. Manually Created Schema
  2. These schemas that are created by you or say by the database administrator for the end users who need to work with your database such as Database Developer.
    You can watch tutorial 37 on how to create user using ‘Create User’ statement and tutorial 38 on How to create user using ‘SQL Developer’ tool for a better understanding.

  3. Sample Schema or Users
  4. These schemas come preinstalled with your database for practice purposes. By default these schemas are locked. In order to use them you have to first unlock them. In this tutorial we will learn how to unlock sample users in oracle database using ALTER USER DDL statement.

How to Unlock Sample User/ Schema in oracle database.

Oracle database comes with several sample schemas pre-installed for practice purposes. The best thing about these schemas are that they already have dummy data installed and have been created while keeping in mind all the database creation guidelines. This means that you do not have to do anything. Just unlock them and use them.

The most used and famous Sample schema in oracle database is ‘HR’. I will use the same here in this tutorial for demonstrating how to unlock the user. But you can follow the same steps for unlocking any sample user you want and on any platform i.e. Windows, Linux or even Apple Mac.

The following steps are done on Windows Command Prompt. If you want you can use any terminal.

Step 1: Set the Oracle SID.

First you need to set the SID of the database with which you want to connect in your system. In my case the database is ‘ORCL’ which is the default one.

  Set ORACLE_SID=orcl
how to unlock user or schema in oracle database by manish sharma rebellion rider

Step 2: Connect to your database.

Next you have to connect to your database using the user with SYSDBA privileges or any user which either has SYSDBA privileges or system privileges or granted with ALTER USR privilege. In my case I will use my ‘SYS’ user to connect with the database.

Read Here: What are User Privileges and How to Grant Them In oracle Database

 SQLPLUS sys/oracle AS sysdba
how to unlock user or schema in oracle database by manish sharma rebellion rider

Step 3: Unlock user with ALTER USER DDL

Once you are connected to the database with your privileged user, next thing you will have to do is to unlock the user. To unlock the user in oracle database we use ALTER USER DDL statement. The syntax of the ALTER USER DDL statement is very simple:


 ALTER USER username IDENTIFIED BY password ACCOUNT unlock;

ALTER and USER is an oracle reserved phrase followed by the name of the user which you want to unlock. Next is IDENTIFIED BY clause that lets you set a password for your user followed by the clause you have to specify the password which you want to assign for your schema in oracle database. At the end of the ALTER USER DDL statement we have ACCOUNT clause which has two flags lock and unlock. You can use both the flags accordingly.

Example: Unlock HR user.

As I mentioned above that for the demonstration I will unlock the HR user. But you can use the following DDL statement for unlocking any sample user of your oracle database.

Above statement will unlock the HR user and set its password to HR. That’s all you have to do. Now you are all set to use your HR account.

how to unlock user or schema in oracle database by manish sharma rebellion rider

For detailed knowledge please watch the video at the start of this blog that explains the same topic.

Help Others In learning

Hope you enjoyed reading. If so then help others in learning by sharing this article on your social media or you can click her to share this blog on your twitter. Thanks & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn