To Lock/Unlock User In Oracle

We can lock certain Oracle user to prevent that user login to the database. User can be locked by using the following command :

oracle@mytestbed:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:34:57 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER USER testing ACCOUNT LOCK; 

User altered.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Whenever the locked user try to connect, some error code will appear like this :

oracle@mytestbed:~$ sqlplus testing/password

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:35:04 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-28000: the account is locked

Enter user-name: ^C
oracle@mytestbed:~$

To release the lock and let the user login again we simply do it by the following command :

oracle@mytestbed:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:37:59 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER USER testing ACCOUNT UNLOCK;

User altered.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
oracle@mytestbed:~$

Here is the result, user testing can do login again :

oracle@mytestbed:~$ sqlplus testing/password

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:38:11 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

Leave a Reply