ORA-28002 Password Expiration Warning

[Problem description]

What do I do when a user prompts an error ORA-28002 that the password is about to expire?

CIE68 :~ # Su-Oracle
Oracle @ CIE68: ~> System/Atae1234

SQL * Plus: Release 11.1.0.6.0-Production on Tue Jan 19 15:15:50 2010

Copyright (c) 1982,200 7, Oracle. All rights reserved.

ERROR:

ORA-28002: the password will expire within 302 days

Connected:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[Troubleshooting]

Because the password expiration time is set, there will be a password expiration reminder. How can I view and modify the Password Expiration limit?

Step 1 log on to the database and view the user's proifle file name, which is generally default:

Perform the following operations:

SQL> SELECT username, PROFILE FROM dba_users where USERNAME = 'ossmodel ';

USERNAME PROFILE
------------------------------------------------------------
OMSMODEL DEFAULT

 

Step 2. view the password validity period settings of the specified profile (such as default:

SQL> select LIMIT from dba_profiles s where s. profile = 'default' and resource_name = 'password _ LIFE_TIME ';

LIMIT
----------------------------------------
180

Step 3 change the password validity period from the default 180 days to "unlimited ":

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> select LIMIT from dba_profiles s where s. profile = 'default' and resource_name = 'password _ LIFE_TIME ';

LIMIT
----------------------------------------
UNLIMITED

After step 4 is modified, the user who has not been prompted for a ORA-28002 warning will not receive the same prompt again; the user who has been prompted must change the password again, for example:

SQL> alter user <user> identified by <original_password>

---- Note: no new password is required

Example:

SQL> alter user system identified by Atae12345;

User altered.

Note: If the user has a ORA-28002 warning as shown in the Problem description above, you must perform Step 4 after changing the user password to UNLIMITED, re-Modify the user password (the password can be retained with the original), otherwise still reported: ORA-28002 warning;

The ORA-28002 password will expire in 7 days