But the real problem is that now we can't connect as that user, and we can't delete it. When we try to delete it, we get error message "can't drop the user that is currently connected" - which of course he is not (there is no session of that user, and as I said, no one can connect as that guy). Can we somehow repair the user, so that it becomes accessible again or can we drop it with some force option?
SELECT sid,serial# FROM v$session WHERE username='TEST'; For every SID,SERIAL# combination, issue the following: ALTER SYSTEM KILL SESSION 'sid,serial#';
Substitute the SID and serial# for each session returned from the first query into the ALTER SYSTEM command above.
Once you can query V$SESSION and not see any connected sessions, then
you can proceed with dropping the user (DROP USER CASCADE).
This was first published in February 2004