ORA-01940: Cannot Drop A User That Is Currently Connected - Learnomate Technologies

ANKUSH THAVALI
07 Dec, 2023
0 Comments
34 Secs Read

ORA-01940: Cannot Drop A User That Is Currently Connected

Problem :


SQL> drop user SCOTT cascade
2 /
drop user SCOTT cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

1. Find the sessions running from this userid:

SQL> SELECT SID,SERIAL#,STATUS from v$session where username='SCOTT';

SID SERIAL# STATUS
---------- ---------- --------
44 56381 INACTIVE
323 22973 INACTIVE

2. Kill the sessions:

SQL> ALTER SYSTEM KILL SESSION '44,56381' immediate;

System altered.

SQL> ALTER SYSTEM KILL SESSION '323,22973' immediate;

System altered.

SQL> SELECT SID,SERIAL#,STATUS from v$session where username='SCOTT';

no rows selected

3. Now the drop the user:

SQL> drop user SCOTT cascade

user dropped.

Hope it Helps!