Tag Archives: ORA-01940: cannot drop a user that is currently connected

ORA-01940: cannot drop a user that is currently connected [How to Solve]

This error is often encountered when deleting a database user

ORA-01940: cannot drop a user that is currently connected

The reason is that there is a program connecting to the user that we need to delete, so we must disconnect the user before deleting it.

1. Find which connections are running under the account.

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’USERNAME’；

Note: Capitalization is required here

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’SSTEST’;

SID SERIAL#
———- ———-
137 41813
335 25993
464 58747
500 9153
531 50402
629 9264
631 11237
664 36180

8 rows selected.

2. Deleting user processes.

SQL>ALTER SYSTEM KILL SESSION ‘137,41813’;

System altered.

SQL>ALTER SYSTEM KILL SESSION ‘33525993;

System altered.

3, re-view the user connection and confirm that no connection is in use

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’SSCRM’;

4、delete users

drop user sstest cascade ；

JavaScript: How do you draw beautiful terms?>>

Issued by:

SQL}}DROP USER USER1 CASCADE

ERROR: ORA-01940: cannot drop a user that is currently connected

from the user currently connected to data libraries, so it cannot be 21024;

Found:

1, search for the user’s session process,

SQL] (SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=’USER1′;

SID SERIAL#
——————
24 25341

2, end all sessions of the user

SQL ALTER SYSTEM KILL SESSION ‘24,25341’;

System altered.

3, subsequently 21024;, user

SQL] (DROP USER USER1 CASCADE);