I will explain Alter User Account Lock & Unlock in Oracle in this post.
Alter User Account Lock in Oracle
Oracle DBA needs locking any user to prevent login to database, If you want to lock any user, you can lock it as follows.
ALTER USER USER_NAME ACCOUNT LOCK; SQL> ALTER USER MEHMET ACCOUNT LOCK;
Alter User Account Unlock in Oracle
OR Sometimes You can get “ORA-28000: the account is locked ” error.
Details of error are as follows.
ORA-28000: the account is locked Cause: The user has entered wrong password consequently for maximum number of times specified by the user's profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account Action: Wait for PASSWORD_LOCK_TIME or contact DBA
To solve this error, unlock the related user as follows.
ALTER USER USER_NAME ACCOUNT UNLOCK; SQL> ALTER USER MEHMET ACCOUNT UNLOCK;
Or increase the Password Life time parameter as follows.
Check the related profile limit.
SELECT resource_name, limit FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';
Change default password life time to unlimited as follows.
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Check Oracle user account status and expiry date like following.
select username, account_status, EXPIRY_DATE, profile from dba_users where username='DEVECI';
Existing user account status and expiry_date will not change, so you should change user password with original hash value to activate these changes.
To take hash password of user, execute following script.
SELECT password, spare4 FROM SYS.USER$ WHERE name = 'DEVECI'; 4CD10ECBE9E0C21A
You should change related user password with its hash value like following.
ALTER USER DEVECI IDENTIFIED BY VALUES '4CD10ECBE9E0C21A';
Or you can set new password for the user.
alter user username identified by new_password;
Thus, Related Oracle User password lifetime will be unlimited and it will not be expired.
5,367 views last month, 2 views today