This Online Tech Support page is written based on examples to be easier to follow. After logging on to Oracle database you may see error ORA-28002: the password will expire within X days and the reason for that is Oracle databases have set by default on users’ password expiration limit. To change it you would need to amend the Oracle Profile. You can change the password expiring time longer or shorter or turn it off. The syntax to amend password life time is following:
ALTER PROFILE <PROFILE_NAME> LIMIT PASSWORD_LIFE_TIME UNLIMITED;
The first thing to do is to find out your user profile name and the SQL syntax below will help you out replace the “<username>” with your own one as on the example below the syntax.
SELECT PROFILE FROM DBA_USERS WHERE USERNAME = '<username>';
This Oracle query below finds user SYSTEM profile name:
SELECT PROFILE FROM DBA_USERS WHERE USERNAME = 'SYSTEM';
The output above shows that the SYSTEM user belongs to group named “DEFAULT“.
Now to set the profile password limit to never expire you can use the following example. Change the profile name (DEFAULT) to your user profile and run the statement as SYSDBA.
alter profile DEFAULT limit password_life_time UNLIMITED;
Now to active the profile change for username you would need to change the password for one more time.
ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>;
The statement below changes user SYSTEM password to word “SYSTEM“.
alter user SYSTEM identified by SYSTEM;
Also you can check your user expiry date using the next select statement where you should replace the “<username>” to your user:
SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = '<username>';
The Oracle SQL returns expiry date for user SYSTEM and it shows the date is empty and that does mean it is turned off.
SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = 'SYSTEM';
See Also:
Home Oracle Create User