How to lock and unlock user account in Oracle Database

In this article, we will learn how to lock and unlock user account in Oracle Database. We will create a new user account in Oracle database and we will use lock and unlock option for Oracle database user account. If your account is lock then you can not work any types in database.

Syntax: First of all, we will create a new user. We can create new user through the admin.

CREATE USER <user_name>
IDENTIFIED BY [<password> | EXTERNALLY] 
[DEFAULT TABLESPACE<def_tablespace_name>] 
[TEMPORARY TABLESPACE <tmp_tablespace_name>]
[QUOTA (<integer> (K | M) ON <tablespace_name>],... 
[PROFILE<profile_name>] [PASSWORD EXPIRE] 
[ACCOUNT [LOCK |UNLOCK]];

Example:

SQL> Create user abc identified by abc;

SQL> Grant connect,resource to abc;

Syntax: We can lock Oracle database user account through the "account lock".

Alter user user_name account lock;

Example:

SQL> Alter user abc account lock;

SQL> Connect abc/abc

Output:

We can check our account is lock and unlock through the dba_users data dictionary.

SQL> Connect sys/oracle as sysdba

SQL> Desc dba_users

SQL> Select username,account_status,Lock_date
2 from dba_users
3 where username='ABC';

Output:

Syntax: We can unlock Oracle database user account through the "account unlock".

Alter user user_name account unlock;

Example:

SQL> Alter user abc account unlock;

SQL> Select username,account_status,Lock_date
2 from dba_users
3 where username='ABC';

SQL> Connect abc/abc

Output: 

Image1

The Users page is displaying. We will select the username and click the actions dropdown arrow button. This is show the list you can select any one.

1. Lock User:  The lock user option we will select from the actions list, and then click Go.

2. Unlock User:  The unlock user option we will select from the actions list, and then click Go.

Image2

The Confirmation page is displaying. You want to change then click Yes otherwise No.

Image3

The Update Message page is displaying. This page give the message " User ABC has been Locked successfully".

Image4