Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Users’ passwords are valid for the duration of PASSWORD_LIFE_TIME in their profile. At the end of this period the password expires and must be changed. After the password expires, the user’s status will be in EXPIRED(GRACE) State until the day of PASSWORD_GRACE_TIME in the profile.
Detailed information about Oracle profiles can be found in the following article.
“How To Create Oracle Profiles”
You can create the necessary command with the following query to change user status from EXPIRED (GRACE) to NORMAL by keeping the current password.
1 2 3 4 5 6 7 | SQL> SET HEADING OFF SQL> SET LINES 1000 SQL> select 'alter user ' || su.name || ' identified by values' || ' ''' || spare4 || ';' || su.password || ''';' from sys.user$ su join dba_users du on ACCOUNT_STATUS like 'EXPIRED%GRACE%' and su.name = du.username; alter user ORCL1 identified by values 'S:8CF4DFE733402BC9EEFDEDB120110747743E76C08118A06EA04EE6BAD080;7117215D6BEE6E82'; alter user AHMET identified by values 'S:61E05318E080C4A5E413DC3CE5D842F4342A4B20320727FAE1E587DF3446;CE4A36B8E06CA59C'; alter user SCOTT identified by values 'S:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67'; |
By running the alter user commands from the output, the user’s current password will be retained and the status will be changed to normal.
1 2 3 | SQL> alter user AHMET identified by values 'S:61E05318E080C4A5E413DC3CE5D842F4342A4B20320727FAE1E587DF3446;CE4A36B8E06CA59C'; User altered. |
If the user status is EXPIRED, you must add ACCOUNT UNLOCK to the end of the command. The sample script would be as follows.
1 2 3 | SQL> alter user AHMET identified by values 'S:61E05318E080C4A5E413DC3CE5D842F4342A4B20320727FAE1E587DF3446;CE4A36B8E06CA59C' account unlock; User altered. |
