Pages

Monday, January 7, 2013

ORA-28002: the password will expire within 7 days

When you try to connect to Oracle and you're getting the following error:

$ sqlplus "sys/password"
...
ERROR:
ORA-28002: the password will expire within 7 days
...


Then you might want to change the expiry date for the user's profile. First things first, check the current status and the profile for the users account (in my case the sys user itself):

SQL> select username,account_status,expiry_date,profile from dba_users where username = 'SYS';

USERNAME  ACCOUNT_STATUS  EXPIRY_D PROFILE
--------- --------------- -------- -------
SYS       OPEN            01/14/13 DEFAULT


Todays date is the 01/07/13 so the sys account will be locked in seven days. Each user is assigned to a profile. The profile holds the information when to expire a password. The sys user has the default profile so I needed to change the default profile:

SQL> alter profile DEFAULT limit password_life_time unlimited;

Checking the sys user again shows that no expire date is set for the password.

SQL> select username,account_status,expiry_date,profile from dba_users where username = 'SYS';

USERNAME  ACCOUNT_STATUS  EXPIRY_D PROFILE
--------- --------------- -------- -------
SYS       OPEN                     DEFAULT


Just keep in mind that the default profile may be assigned to multiple users. For all these users the password will not expire anymore!
If you still get the above ORA-28002 then take a closer look at ACCOUNT_STATUS. In my example above it is open. When the ACCOUNT_STATUS is already expired then you have to reset the password first:

SQL> select username,account_status,expiry_date,profile from dba_users where username = 'SYS';

USERNAME  ACCOUNT_STATUS  EXPIRY_D PROFILE
--------- --------------- -------- -------

SYS       EXPIRED(GRACE)  12.04.13 DEFAULT

SQL> alter user sys identified by password;


SQL> select username,account_status,expiry_date,profile from dba_users where username = 'SYS';

USERNAME  ACCOUNT_STATUS  EXPIRY_D PROFILE
--------- --------------- -------- -------

SYS       OPEN                     DEFAULT