How Do Turn Off Oracle Password Expiration
  Home
 

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';

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days

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;

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days

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;

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days

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';

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days



See Also:
Home Oracle Create User

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*