Alter User Password - Reset User Password in Oracle

When the database administrator creates a new user in the Oracle database, password for the new user is set to expire. Password expiry occurs, for it is the default in setting inside Oracle. You can query the Oracle database to see the detail of the database profile as:

Select Profile, Resource_Name, Resource_Type, Limit

from dba_Profiles

where Resource_Name like 'PASS%';

Then, look for highlight line. The password expire after 180 days.

Then, you can query the DBA_USERS table to see the expiry date for the user. Using the follow SQL statement:

Select UserName, Account_Status, Expiry_Date

from dba_Users

Where UserName = 'TEST';

The above query will show the account status and password expiry date for user TEST.

The password for the user TEST shall expire on Sep 03, 2011. What happen when your user password expires. The user cannot log in. Let's try by altering the user TEST account status to expire. Log in the Oracle database as SYSDBA in SQL Plus, and issue alter statement as follow:

Alter User TEST Password Expire;

Now the user TEST shall not be able to connect to the database. Here is the result after altering password to expired

You can unlock the account for user TEST by resetting the new password. At the SQL Plus command prompt, issue the following command to reset password

Alter User TEST identified by NewPassword;

The user TEST now can access the database again.....