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

ORA-01940

Tried to drop an user with all its contents cascadingly, but it failed with ORA-01940.

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

ORA-01940 means that some users are still connecting to the account that you want to drop, so Oracle cannot drop it in this moment.

Let's see who are still connecting to it.

SQL> column machine format a30;
SQL> column osuser format a20;
SQL> select machine, osuser from v$session where username = 'HR';

MACHINE                        OSUSER
------------------------------ --------------------
WORKGROUP\LAPTOP-B0A8Y34T      scott

As you can see, there's one session connecting to the account.

Solution

All sessions connected to the account need to be disconnected before we drop the user. You may ask them to quit theirs session normally. Or just kill the sessions, if they don't mind.

Here we compose some statements of killing session.

SQL> select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' stmts from v$session where username = 'HR';

STMTS
--------------------------------------------------------------------------------
alter system kill session '1725,53875' immediate;

Then we issue the statements.

SQL> alter system kill session '1725,53875' immediate;

System altered.

Now we can do our job.

SQL> drop user hr cascade;

User dropped.

Sometimes, you may still get the same error message. Please wait for a moment, it could be a time lag after killing the sessions.