Jun 06 2017 11:34 AM by
Hello guys, welcome to oracleplsqlblog.com. This blog will help you to unlock any user account in Oracle database.
You might be getting this error when you are trying to connect to an account that is locked, an account can be locked because of various reasons one of them being multiple wrong password attempts, now the number of wrong attempts that leads to account lock depends upon the password policy in place. But, no need to worry about all that, here we will learn on unlocking the account.
You can even get this error when you are trying to access any Oracle shipped schema such as HR, which is by default locked.
So to unlock any account, you need to connect to any account that has SYSDBA privileges, because the query we will be executing can only be executed using SYSDBA privileges.
To connect, go to command prompt (cmd) and connect to the account with SYSDBA privileges as follows:
SQLPLUS SYS AS SYSDBA
Next, it will prompt for the password, enter the SYSDBA password(case sensitive), many times DBA's don't update the default password i.e manager.
Once you are connected to unlock the account execute the following statement(here username has to be updated with the actual schema name):
ALTER USER user_name ACCOUNT UNLOCK;
ALTER USER HR ACCOUNT UNLOCK;
If you do not remember the password and want to change the password along with unlocking the account, execute the below statement with 'user_name' as the schema name whose account is to be unlocked and replace 'password' with the actual password that you want to update:
ALTER USER user_name IDENTIFIED BY password ACCOUNT UNLOCK;
ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
If you wish to simply update password of any user, execute the below statement, always remember that the passwords are case sensitive:
ALTER USER user_name IDENTIFIED BY password;
ALTER USER scott IDENTIFIED BY tiger;