Thursday, April 14, 2011

ORA-01940 cannot drop user

Problem:
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 : http://www.dba-oracle.com/t_ora_01940_cannot_drop_user.htm
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';

Wednesday, April 13, 2011

path oracle 10g

Problem:
You can't execute commands such as "imp", "exp", "impdp" or "expdp" even though the path is setted in the environment variable.

Solution:
In my case, there was a mistake. When I installed Oracle 10g, the path was automatically set to :
PATH=C:\oraclexe\app\oracle\product\10.2.0\server\bin;C:\Windows\system32...

But in reality it should be "BIN" (uppercase)!
PATH=C:\oraclexe\app\oracle\product\10.2.0\server\BIN;C:\Windows\system32...

After modifying the path, I could execute the command "impdp" anywhere from the command line.