🧩 How to Find Locked Users in Oracle Database

🧩 Introduction

Sometimes database accounts get locked due to too many failed login attempts or for security reasons. As a DBA, you need to quickly identify locked users to restore access or investigate issues.

Step 1: Query Locked Accounts

SELECT username, account_status

FROM dba_users

WHERE account_status LIKE 'LOCKED%';

➡️ This will list all users whose accounts are locked.

Step 2: Unlock a User Account

ALTER USER username ACCOUNT UNLOCK;

➡️ Replace username with the actual username to unlock.

Step 3: Reset Password if Needed

ALTER USER username IDENTIFIED BY new_password;

➡️ Use this if the account was locked due to failed login attempts and the user needs a new password.

Conclusion

Monitoring and managing locked accounts is an essential task for DBAs to maintain both security and usability in Oracle databases.