When you Trying to reset to the existing password to the oracle user ,You may encounter below error .
SQL> alter user nadhan IDENTIFIED BY 'xxxxxx';alter user nadhan IDENTIFIED BY 'xxxxxxx'
*
ERROR at line 1:
ORA-28007: the password cannot be reused
Solution:
Reason could be that the user's profile options .
Check the user profile set for the user. Check for the existence of a password verify function in the profile.
Set the password_verify_function to NULL
Another reason could be that the PASSWORD_REUSE_MAX limit has reached. If so set to a higher value or to UNLIMITED and reset the password as shown below.
Check user profile:
select * from dba_profiles where profile='USER_PROFILE';
select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = 'nadhan';
select * from dba_profiles where profile='USERNAME' and RESOURCE_NAME in
('PASSWORD_REUSE_TIME');
Change below profile values .
SQL>alter profile USER_PROFILE limit password_verify_function NULL;
SQL> alter profile USER_PROFILE limit PASSWORD_REUSE_MAX unlimited;
Now you can set user passwords same as earlier .
Once the password is changed make it to default...
SQL>alter profile USER_PROFILE limit password_verify_function DEFAULT;
SQL>alter profile USER_PROFILE limit PASSWORD_REUSE_MAX DEFAULT;
If you don't know the existing user password and still you want to set it as earlier one .
Below are the steps ...
1. Note down the password value by using below script .
SQL> select password from user$ where name='NADHAN' ;
PASSWORD --nadhans
------------------------------
5J53351LKJIH9AEE7
2. Check User status .
SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where USERNAME like '%NADHAN%';
USERNAME PASSWORD ACCOUNT_STATUS EXPIRY_DATE
--------- ------------------------------------------- ------------------------------------
NADHAN EXPIRED 24-AUG-21
3. Set temporary passwords to make it as active .
SQL> alter user NADHAN identified by Test123#BB ;
4. Check status now ,It should be OPEN.
SQL> select account_status from dba_users where username='NADHAN' ;
ACCOUNT_STATUS
--------------------
OPEN
5. Now alter the password by using VALUES .
SQL> alter user ITCNIMBUS identified by VALUES '5J53351LKJIH9AEE7' ;
User altered.
6.Check User status now .
SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where USERNAME like '%NADHAN%';
USERNAME PASSWORD ACCOUNT_STATUS EXPIRY_DATE
--------- ------------------------------------------- --------------------------------------------------
NADHAN OPEN 24-AUG-21
Now users can connect or use old password .
