Oracle - Avoid user password expiration

When you connect to Oracle you see this warning: "The password wil expire within %s days"

Check all profiles:

select * from dba_profiles

Check the profile assigned to one user:

select profile from DBA_USERS where username = 'MyUser';

For this profile, filter its limits:

select resource_name,limit from dba_profiles where profile='DEFAULT';

Check user account status and expiration date for this user:

select username, account_status, expiry_date from dba_users where username='MyUser';

With this information we know that our user is assigned to the Default profile, which has an expiration policy of 180 days. Also, this user´s password will expire on 09/01/2018. To avoid this, just update the profile´s policy.

alter profile DEFAULT limit PASSWORD_LIFE_TIME  unlimited;

The last thing to do is reset the password to its own value, in order to restart the account status to OPEN. Depending on your Oracle version, you can obtain this password by two means:

select password from dba_users where username='MyUser';

select spare4 from sys.user$ where name='MyUser';

Only one of them will return a non NULL value. Then update the user.

alter user MyUser identified by values 'S:9090878D...;T:193A657B...';