We have a problem dropping users. We use Oracle 18.104.22.168 on Solaris. Once we issued the command, drop user test cascade, it took too long, we interupted it and now it seems that user is in some corrupted state. We can see him, for example, in the list of users in Enterprise Manager, but not in the list of table owners...
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?
You'll have to verify that no one is connected as that user. Issue the query below:
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).
Dig deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.