ORA-01940 cannot drop a user that
is currently connected tips
Oracle Tips by Burleson Consulting
December 18, 2007
Question: I am trying to drop a user and I get
the error "ORA-01940: cannot DROP a user that is currently logged in". The
problem is that the user is not connected according to v$session. I
suspect that it's related to my replication, and the user is a propagator.
How do I get around this ORA-01940 error?
Answer: First, be very careful when dropping a user
who owns replication, as it can cause you to have to completely redo the
replication definitions. The docs note:
ORA-01940: cannot DROP a user that is currently logged
Cause: An attempt was made to drop a user that was currently logged
Action: Make sure the user is logged out, then re-execute the
The ORA-01940 can always be cured by bouncing the source
and replicated instance. First, double-check to ensure that the user is
not connected to the current instance.
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#>';
Also, check that the user is not associated with any active
select job from dba_jobs where log_user='myuser';
Finally, check that the user
is not associated with any Streams replication queues:
Once you release the user from the propagation replication
they can be dropped, but you must then re-define the replication mechanism.