How to re-open expired oracle database account without change password

Imagine that you have an expired Oracle Database account and you have an urgent need to recover it. Maybe you don’t have any chance to ask that user to change password, or maybe you expired such an account that used by a production application by mistake!

-yes, I confess; it was me who experienced that situation-

If so, do what I did; calm down and take a deep breath and imagine that you are the one who survived after a big disaster on the earth. Just kidding, it’s not a big trouble.

Anyway, I couldn’t find any documented solution for that by Oracle, but found a way to accomplish this task:

Alter user’s password with existing password. Yes, it works. Let’s make a short demo:

1. Create a user
2. Expire it
3. Alter user’s password with existing password to unexpire

1. CREATE AN ACCOUNT NAME TEST

SQL> CREATE USER TEST IDENTIFIED BY "manager";

User Created

SQL> SELECT username,account_status,expiry_date FROM dba_users WHERE username='TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------- -------------- -----------
TEST OPEN

2. EXPIRE IT
 
SQL> ALTER USER TEST PASSWORD EXPIRE;

User altered.

SQL> SELECT username,account_status,expiry_date
FROM dba_users
WHERE username = 'TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------- -------------- ------------
TEST EXPIRED 19/12/2012
 
3. GET THE EXISTING PASSWORD

SQL> SELECT DBMS_METADATA.get_ddl ('USER', 'TEST')
FROM DUAL;

CREATE USER "TEST" IDENTIFIED BY VALUES 'S:24BF9B8EBC5BC5B609D01915E089AF86D13E1F0627A729146708F5296E32;806B1E705874556A'

DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
 
4.EDIT ABOVE SCRIPT TO HAVE ALTER STATEMENT AND EXECUTED IT

SQL> ALTER USER "TEST"
IDENTIFIED BY VALUES 'S:4F5BD279183624CB94C3E67420A1E748A484183579F89E64B98ECB17A162;806B1E705874556A';

User altered.

SQL>SELECT username,account_status, expiry_date FROM dba_users WHERE username = 'TEST';

USERNAME ACCOUNT_STATUS EXPIRY_DATE

-------- -------------- -----------
TEST OPEN


5.OPTIONALLY DELETE TUTORIAL OBJECT

SQL> Drop user TEST cascade;

I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!