Alter User Account Lock & Unlock in Oracle

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.

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

See more