The Oracle RDBMS provides the ability to lock user accounts for various reasons when the administrator or organizational policy determines it necessary. When an account is locked the user will be notified upon connection attempt with the Oracle error ORA-28000. This article describes the process of unlocking a user account so the user to regain access.
1. The user notifies you that they has received the error ORA-28000 when attempting access the database.
SQL>connect scott/tiger
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
2. Your first action should be to verify this information within your database. Connect to the specified database with sysdba privileges.
SQL>connect ljcatt/xxxxx@mydb as sysdba
Connected.
SQL>
3. Perform a select on the view DBA_USERS with the following SQL: select account_status from dba_users where username=’SCOTT’;
SQL>select account_status from dba_users where username='SCOTT';
ACCOUNT_STATUS
--------------------------------
LOCKED
SQL>
4. To unlock the account execute the following SQL: alter user scott account unlock;
SQL>alter user scott account unlock;
User altered.
SQL>
5. Verify the account status is now open.
SQL>select account_status from dba_users where username='SCOTT';
ACCOUNT_STATUS
--------------------------------
OPEN
SQL>
6. This completes unlocking a user account after receiving ORA-28000.
Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com