Tuesday, 10 November 2015

ORA-01940 cannot drop a user that is currently connected

You cant not drop a user directly which is currently connected or in use.
 SQL> drop user username ;
ORA-01940 cannot drop a user that is currently connected

    Two way to drop the user.
1. Either disconnect all the connected session manually. or

2. Kill the all the session which are currently connected.

  SQL> select sid, serial#, username, machine from V$session where username='SCHEMA_NAME';

  SID               SERIAL#             USERNAME                           MACHINE
------------    ----------------------   --------------------------------       ------------------------------------------------------
 234                 3452                   DLT                                      spgwtrd

SQL> alter system kill session '234,3452' immediate;
system altered.

No comments:

Post a Comment