ORA-01940: cannot DROP a user that is currently logged in
Cause: An attempt was made to drop a user that was currently logged in.
Action: Make sure the user is logged out, then re-execute the command.
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:
Once you release the user from the propagation replication they can be dropped, but you must then re-define the replication mechanism.