In spite of deleting an oracle user, we can LOCK it. In that way the user won’t be accessible. In future if we want we can UNLOCK it.

create a user named JACK which is LOCKED:

SQL> create user jack identified by jack account lock;

User created.

Now grant him the CONNECT & RESOURCE privilege:

SQL> grant connect, resource to jack;

Grant succeeded.

Now, try to connect the user:

SQL> conn jack/jack;
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

Now, connect to the SYS accout:

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

Now, see the account status:

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME=’JACK’;

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE
------------------------------ -------------------------------- ---------
JACK                           LOCKED                           10-OCT-08

To, unlock the Jack:

SQL> alter user jack account unlock;

User altered.

SQL>
SQL> conn jack/jack;
Connected.

==================

Otherwise we can do one thing, we can revoke its CONNECT privilage. In that way the user can’t connect to the db.

SQL> revoke connect from jack;

Revoke succeeded.

SQL> conn jack/jack;
ERROR:
ORA-01045: user JACK lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> grant connect to jack;

Grant succeeded.

SQL>
SQL> conn jack/jack;
Connected.

>>>Please leave your commant on the post.