Oracle Database: Unlock user account via SQLPlus

As stated in "Oracle Database: Change Password Expiration" when installing DB I usually change password expiration for DEFAULT profile from 180days to UNLIMITED.

This is because if you create users that uses DEFAULT profile after 180days Oracle gives you:

ORA-28001: the password has expired

So...you can edit DEFAULT profile in two ways:

1) Via Enterprise Manager Console as stated in  Oracle Database: Change Password Expiration
2) Via SQLPlus

In this post I explain how to recover a locked user via SQLPlus and set DEFAULT profile with an unlimited password expiration time.

Connect to the instance:

[oracle@oracle ~]$ sqlplus

SQL> conn sys/password@sid as sysdba

SQL> select username,account_status from dba_users;  


The above command will return all dba users and their corresponding status presenting them in a view similar to this:

USER1 LOCKED&EXPIRED
USER2 OPEN 

Now let's unlock the user(s):

SQL> alter user [user_name] identified by [password] account unlock;

Then modify DEFAULT profile setting the password expiration time to UNLIMITED:

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;

That's all!!