Thursday, April 14, 2011

ORA-01940 cannot drop user

Oracle 10g, I want to drop a user using this command :
drop user alex cascade;

But I have this error:
ORA-01940: cannot drop a user that is currently logged in

Solution :
I checked on google and find this excellent Website :
It said that you should do this :

select s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p
where s.username = 'myuser'
and p.addr (+) = s.paddr;

alter system kill session 'sid,serial#';

That's correct but your username should be in uppercase!
For example :
where s.username = 'ALEX'

And that's it !

You can do a quick test :

select username from v$session;
select * from v$session where username = 'alex';
select * from v$session where username = 'ALEX';

No comments: