ORA-01940: cannot DROP a user that is currently logged in

Reference: http://www.dba-oracle.com/t_ora_01940_cannot_drop_user.htm

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:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *