ORA-01940 cannot drop a user that is currently connected tips
 

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:

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 jobs:

select job from dba_jobs where log_user='myuser';

Finally, check that the user is not associated with any Streams replication queues:

select
   queue_table,
   qid
from
   dba_queues
where owner='myuser';

select
   apply_name
from
   dba_apply
where
   queue_owner='myuser'; 

select
   capture_name,
   queue_name
from
   dba_capture
where
   queue_owner='myuser';

select
   propagation_name
from
   dba_propagation
where
   source_queue_owner='myuser'
or
   destination_queue_owner='myuser';
 

Once you release the user from the propagation replication they can be dropped, but you must then re-define the replication mechanism.