Problem solve Get help with specific problems with your technologies, process and projects.

Dropping users

We have a problem dropping users. We use Oracle 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:


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 last published in February 2004

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.