Oracle 11g - A Solution To The Password Will Expire Error - ORA-28002

Hello everbody!

Today I have seen an error which tells me my Oracle 11g database user's password will expire in a day! So I had to sort this out before I did any work with the database. I also decided to write a blog post about the solution. Here we go.. This is the error message I got and here is the solution.

STEP 1 - Connect As a SYSTEM USER

Type this and connect : connect / sysdba

STEP 2 - Check Your User's Profile

We need to check if the user we want to sort out is using the "DEFAULT" profile. For that simply run this command: select profile from dba_users where username = 'SELEN';

As you can see above, the user's profile is DEFAULT. Note that the username value is case sensitive. That is why I got an error when I type the username lowercase like - username='selen'-.

STEP 3 - Check and Configure The "Password Life Time" Of The DEFAULT Profile.

Normally password_life_time is set to 180 by default. We now going to check and update the value to UNLIMITED. Type this command: select resource_name,resource_type, limit from dba_profiles where profile='DEFAULT';

And you will be able to see password_life_time is set to 180. Now we are going to update this value by using the following command: alter profile DEFAULT limit password_life_time UNLIMITED;

After setting it to unlimited, it will never expires but this may not be a very safe solution if you are working in a production environment. So you should consider this before making the change.

STEP 4 - Last Move To Go

If you try to connect now, you are still getting the same “ERROR: ORA-28002: the password will expire” message like below.

So to get rid of this we need to alter the password by using this command: alter user SELEN identified by "password"; Now try to connect and it will work fine.

Hope to see you in the next blog post!

Selen