23

I have a user getting an ORA-28002 indicating that the password will expire within six days. I ran the following:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

But when I try to log in as the user, the message is still there. Executing this:

select * from dba_profiles where RESOURCE_NAME LIKE 'PASSWORD_LIFE_TIME';

shows that the values was really changed to UNLIMITED.

Improve this question

7 Answers 7

Reset to default
19

The password has been marked as 'EXPIRED' or marked with an 'EXPIRY_DATE' in dba_users. You will have to change it. You can set it back to the same password. The easy way would be setting the password "by values". This usually bypasses history checking.

12:28:33 SQL> select * from dba_users where username = 'MYUSER1';

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE          EXPIRY_DATE
-------------------------------- ------------------ ------------------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ------------------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
MYUSER1                               338 66856982BE5CD23F
OPEN
USERS                          TEMP                           17-JAN-11
DEFAULT                        DEFAULT_CONSUMER_GROUP



Elapsed: 00:00:00.03
12:28:43 SQL> alter user myuser1 password expire;

User altered.

Elapsed: 00:00:00.00
12:29:11 SQL> select * from dba_users where username = 'MYUSER1';

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE          EXPIRY_DATE
-------------------------------- ------------------ ------------------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ------------------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
MYUSER1                               338 66856982BE5CD23F
EXPIRED                                             17-JAN-11
USERS                          TEMP                           17-JAN-11
DEFAULT                        DEFAULT_CONSUMER_GROUP



Elapsed: 00:00:00.03
12:29:14 SQL>

The format of this is "alter user USER identified by values 'hash from dba_users.password';"

[TEST] C:\>sqlplus system

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 17 12:18:16 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

new: showmode BOTH
12:18:17 SQL> prompt end of LOGIN.SQL
end of LOGIN.SQL
12:18:17 SQL> create user myuser1 identified by mypassword1;

User created.

Elapsed: 00:00:00.01
12:18:21 SQL> grant connect, resource to myuser1;

Grant succeeded.

Elapsed: 00:00:00.01
12:18:30 SQL> connect myuser1/mypassword1
Connected.
12:18:39 SQL> connect system
Enter password:
Connected.
12:18:51 SQL> alter user myuser1 password expire;

User altered.

Elapsed: 00:00:00.00
12:19:05 SQL> connect myuser1/mypassword1
ERROR:
ORA-28001: the password has expired


Changing password for myuser1
New password:
Retype new password:
Password changed
Connected.
12:19:16 SQL> connect myuser1/mypassword1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
12:19:21 SQL> connect system
Enter password:
Connected.
12:19:34 SQL> alter user myuser1 identified by mypassword1;

User altered.

Elapsed: 00:00:00.01
12:19:49 SQL> alter user myuser1 identified by mypassword1 password expire;

User altered.

Elapsed: 00:00:00.01
12:20:26 SQL> select username, password from dba_users where username = 'MYUSER1';

USERNAME                       PASSWORD
------------------------------ ------------------------------
MYUSER1                        66856982BE5CD23F

Elapsed: 00:00:00.01
12:20:36 SQL> alter user myuser1 identified by values '66856982BE5CD23F' ;

User altered.

Elapsed: 00:00:00.01
12:21:01 SQL> connect myuser1/mypassword1
Connected.
12:21:13 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Improve this answer
17

Please find the below three queries and run these queries from system database.

// For seeing full user details
SELECT profile FROM dba_users WHERE username = 'SYSTEM';

// This query is used to change the password life time to unlimited
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 

// This query is used to change the default password.
alter user SYSTEM identified by "system";
Improve this answer
1
  • 1
    Pretty easy and well explained.
    – S. Mayol
    Sep 12, 2016 at 1:28
5

A coworker using Toad saw the account indicated as looked in the Schema Browsers under Users.

We succeeded by right clicking unlock user. In the Alter user dialog we reentered the old password and unchecked the Password is locked checkbox.

Improve this answer
5

I know this is old, but for those using Oracle SQL Developer follow these step:

  • Use another user to log into any database connection you have, you can use the default "system" or "sys" username with whatever password you got there.

  • Open connection tree and find the "Other Users" node and open that like so :

enter image description here

  • Find your username in the child notes under "Other Users" and right click to edit user like so:

enter image description here

  • Type in your new password and Un-check password expired, then apply. Right click edit user again to see if the settings were changed if you wish.
Improve this answer
3

You need to change the password again for the new limit to take effect.

Improve this answer
2
  • I want to keep the same password
    – bernd_k
    Jan 17, 2011 at 17:19
  • 1
    The answer from REW meets this additional requirement assuming you do not have a profile preventing password re-use. If you are on 11g or later the password hash is not in dba_users, so you will need to get it from sys.user$. See laurentschneider.com/wordpress/2007/08/…
    – Leigh Riffel
    Jan 17, 2011 at 18:10
2

You should alter the user account. First connect to your sys account and from there you can perform below mentioned step:

alter user practice identified by password;

this will resolve your issue...

Improve this answer
0

Suppose you are getting this error, very simple fix: connect to database. suppose sample and sample is your oracle user id and password.

ashutosh@LINUX:/oracle/path#sqlplus sample/sample

ERROR: ORA-28002: the password will expire within 7 days

Last Successful login time: Tue Jan 10 2023 23:51:33 -05:00

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0

SQL> password

Changing password for SAMPLE

Old password:

New password:

Retype new password:

Password changed

SQL>

your password will be change.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.