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.