Unlock User or Schema In Oracle Database Using ALTER USER statement.
Users that are also known as schemas are required when you work with your database. They let you connect with the database and access the stored data.
Types of Schema/Users In oracle Database
There are two types of Schemas in oracle database.
- Manually Created Schema
These schemas that are created by you or by the database administrator for the end users such as database developers who need to work with your database.
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.
- Sample Schema or Users
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
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 USER 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
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:
Syntax
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.
ALTER USER hr IDENTIFIED BY hr ACCOUNT unlock;
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.
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 here to share this blog on your twitter. Thanks & have a great day!
