Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, I will show you how to change users’ password expire policy. By default, all created users in Oracle Database will be expired in periodic times. Password expired settings are defined in SQL Profiles.
You can list expired user by executing below command.
1 2 3 4 5 6 7 8 9 | SQL> select username,account_status from dba_users where account_status like '%EXPIRE%' order by created; USERNAME ACCOUNT_STASUS OCINAR EXPIRED & LOCKED OUTLN EXPIRED & LOCKED DIP EXPIRED & LOCKED ORACLE_OCM EXPIRED & LOCKED ...... 25 rows selected. |
As you see, “OCINAR” user had expired. I do not want this user be expired again. So, I will list “OCINAR” profile and change password_life_time parameter to “UNLIMITED”.
1 2 3 | SQL> select username, profile from dba_users where username='OCINAR'; USERNAME PROFILE OCINAR DEFAULT |
“OCINAR” user’s profile is DEFAULT. By changing password_life_time parameter to UNLIMITED, all users defined to DEFAULT profile will not be expired again. I recommend you to create new profile and change user’s profile to the new profile. However, in this example, I will change DEFAULT profile, and all users will be affected with this parameter.
1 | SQL> alter profile DEFAULT limit password_life_time UNLIMITED; |
“OCINAR” user is still expired. I need to activate the user by changing the user password, so it will not be expire again. There should be a password after “identified by”. In our script, the password is ocinar.
1 | SQL> alter user ocinar identified by "ocinar" account unlock; |
Now, “OCINAR” is active and can connect database again. It will not be expired again.
