SELECT * FROM DBA_USERS WHERE ACCOUNT_STATUS <> 'OPEN'; -- Unlock a user ALTER USER my_user ACCOUNT UNLOCK; -- If password is expired ALTER USER my_user IDENTIFIED BY new_password ACCOUNT UNLOCK;
Here is what the above code is Doing:
1. First, we query the DBA_USERS view to find all users that are locked.
2. Then, we unlock the user by using the ALTER USER statement.
3. Finally, if the user’s password is expired, we use the ALTER USER statement to unlock the user and change the password.