Q

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?
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close