Ask the Expert

Dropping users

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

    Requires Free Membership to View

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).

This was first published in February 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: