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?
In my previous article I have given details about application support engineer day to day…
In my previous articles I have given the roles and responsibilities of L1,L2 and L3…
In my previous articles i have given the hierarchy of production support in real company…
In this article i would like to provide information about production support organization structure or…
In my previous article I have given roles for L1 and L2 support engineer with…
I have started this new series of how to become application support engineer. This article…