How to recover the expired user in Oracle?

In my previous articles i have given idea about the different questions related to oracle. In this article i would like to throw light on most important interview question – How to recover the expired user in Oracle?Think of the situation where you have an expired oracle database account and we have to recover that account on urgent basis.

Real life situation :

Sometimes DBA dont know about the situation of production users and by mistake they used expired account for production application environment and assign that to particular user. We do not have any documented resolution for these kind of situations but we could deal with the situation and follow the following steps :

Step 1 : To create the specific user to implement the scenario

Create User Amit Identified by “Amiet@123456”;

Step 2 : Check the status of the user using following query,

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = ‘Amit’;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
—————————— ——————————– ———
Amit OPEN                             12-AUG-20

Step 3 : We require to Expire the User using following command

ALTER USER Amit PASSWORD EXPIRE;

Now the account of Amit is Expired. We require to check the status of the account,

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = ‘Amit’;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
—————————— ——————————– ———
Amit EXPIRED 12-AUG-20

If you can check the account_status it is showing as Expired.

The above scenario will explain about how do we expire the user password and account.

Resolution of this scenario :

Step 1 : We require to fetch the current password of the user Amit as first step. You need to log in with the DBA privilleges,

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = ‘AMIT’; 

You will get the status of the user using above query.

Query to get password in encrypted format :

SELECT DBMS_METADATA.get_ddl (‘USER’, ‘AMIT’) as “User_Info”
FROM DUAL;

You will get the output as below :

User_Info
——————————————————————————–
CREATE USER “Amit” IDENTIFIED BY VALUES ‘S:1256778ASDESADDADDFFFDFDFSFT24FFFASSDFFFBB2;B9C89A643D04449E’
TEMPORARY TABLESPACE “TEMP”
PASSWORD EXPIRE

Step 2 : You will get password in encrypted format and following will be the password :

S:1256778ASDESADDADDFFFDFDFSFT24FFFASSDFFFBB2;B9C89A643D04449E

Step 3 : Alter the specified user

ALTER USER “AMIT”
IDENTIFIED BY VALUES ‘S:1256778ASDESADDADDFFFDFDFSFT24FFFASSDFFFBB2;B9C89A643D04449E‘;

User altered.

Now check the status of the user which will show OPEN.

Step 4 : Other Query to alter the user

                                                      
select name, password from user$ where name=’AMIT’;

NAME                           PASSWORD
—————————— ——————————
AMIT B9C89A643D04449E

alter user AMIT identified by values ‘ B9C89A643D04449E‘;

User altered.

You may test the user status again and it will be now open. So if these kind of situation occurs you may just alter the user and update it to current password in oracle. I hope you understand the answer of the question –How to recover the expired user in Oracle?