<\/p>\n
In this article, I will show you how to change users’ password expire policy.\u00a0By default, all created users in Oracle Database will be expired in periodic times. Password expired settings are defined in SQL Profiles.
\nYou can list expired user by executing below command.<\/p>\n
SQL> select username,account_status from dba_users where account_status like '%EXPIRE%' order by created;\r\nUSERNAME ACCOUNT_STASUS\r\nOCINAR EXPIRED & LOCKED\r\nOUTLN EXPIRED & LOCKED\r\nDIP EXPIRED & LOCKED\r\nORACLE_OCM EXPIRED & LOCKED\r\n......\r\n\r\n25 rows selected.<\/pre>\n<\/p>\n
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”.<\/p>\n
SQL> select username, profile from dba_users where username='OCINAR';\r\nUSERNAME PROFILE\r\nOCINAR DEFAULT\r\n<\/pre>\n<\/p>\n
“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<\/strong>.<\/em> 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.<\/p>\n
SQL> alter profile DEFAULT limit password_life_time UNLIMITED;\r\n<\/pre>\n<\/p>\n
“OCINAR” user is still expired. I need to activate the user by changing the user password, so it will not be expire again.\u00a0There should be a password after “identified by”. In our script, the password is ocinar.<\/p>\n
SQL> alter user ocinar identified by \"ocinar\" account unlock;\r\n<\/pre>\n<\/p>\n
Now, “OCINAR” is active and can connect database again. It will not be expired again.<\/p>\n\n
<\/div>\n