ORA-28002: the password will expire within 1 days

If you use JDeveloper^[1](i.e., Database Navigator) or SQL Developer^[2], you might find the above dialog warns you that your user's account is about to expire.  In our case, fusion user's account will expire within one day.  Here are the steps we use to correct the issue.

How to Check the Expiry Date of User's Accounts?

For Oracle Fusion installations, you need to change all fusion-related accounts if you find one of them (i.e. "FUSION") has expired or is going to expire.  To find the account status and its expiry date, you can use:

SQL> select username, account_status, lock_date, expiry_date from dba_users;

USERNAME                       ACCOUNT_STATUS            LOCK_DATE     EXPIRY_DATE

------------------------------ ------------------------  ------------- -----------
...             

FUSION_MDS                     EXPIRED(GRACE)                          10-APR-12

FUSION_WEBCENTER               EXPIRED(GRACE)                          10-APR-12

FUSION_ACTIVITIES              EXPIRED(GRACE)                          10-APR-12

FUSION_ORA_ESS                 EXPIRED(GRACE)                          11-APR-12

FUSION                         EXPIRED(GRACE)                          10-APR-12

How to Extract the Encrypted Password?

As DBA, you can extract the encrypted password from the dba_users view, save it, and reset the password after you have finished your testing.  In our case, we are conducting performance test using ATG CRMDemo application and our fusion-related accounts have expired.  So, what we need to do is just resetting the password.  Here are the steps that we have used.

To extract the encrypted password for "FUSION", you use^[3]:

select

  'alter user "'||username||'" identified by values '''||

  extract(xmltype(dbms_metadata.get_xml('USER',username)),

  '//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password

from

   dba_users

where

username = 'FUSION';

OLD_PASSWORD

-------------------------------------------------------------------

alter user "FUSION" identified by values '7F749E2EB6A437D3';

Now what we need to do next is just resetting the password to extend the expiry date by six months:

SQL>alter user "FUSION" identified by values '7F749E2EB6A437D3'; 

alter user "FUSION" succeeded.

Note that you need to be the "sys" user to reset all other fusion-related accounts.  Otherwise, you will get:

SQL Error: ORA-01031: insufficient privileges

For example, when we tried:

SQL> alter user "FUSION_DYNAMIC" identified by values 'DC7B1297F45F8FA0';

We have seen the following error:

SQL Error: ORA-01031: insufficient privileges

01031. 00000 -  "insufficient privileges"

... 

          
After resetting all fusion-related accounts, we now have all account opened as shown below:

SQL> select username, account_status, lock_date, expiry_date from dba_users;

USERNAME                       ACCOUNT_STATUS            LOCK_DATE     EXPIRY_DATE

------------------------------ ------------------------  ------------- -----------
...             

FUSION_MDS                     OPEN                                    06-OCT-12

FUSION_WEBCENTER               OPEN                                    06-OCT-12

FUSION_ACTIVITIES              OPEN                                    06-OCT-12

FUSION_ORA_ESS                 OPEN                                    06-OCT-12

FUSION                         OPEN                                    06-OCT-12

References

1. Oracle JDeveloper 11gR2 Cookbook by Nick Haralabidis 
2. Oracle SQL Developer
3. Oracle passwords - changing, expiring and locking users
4. Oracle: How to Configure User Accounts to Never Expire? (Xml and More)