Oracle acts out. Can't drop a user.

[John Landon]

Hi,
Here is what I am trying to do. I just want to drop the damn schema.

C:\>sqlplus /nolog
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 17 16:19:34 2008
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
SQL> conn / as sysdba
Connected.
SQL> drop user ABC cascade;
drop user ABC cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
 
 
SQL> select sid, serial# from v$session where username = 'ABC';
 
       SID    SERIAL#
---------- ----------
       487      43726
 
SQL> alter system kill session '487,43726';
alter system kill session '487,43726'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

[Paul Campbell]

The ORA-01940 can be resolved by bouncing the source and replicated instance.

But FIRST, be sure 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 = 'ABC' 
   and p.addr (+) = s.paddr;

Check that the user is not associated with any active jobs:

select job 
  from dba_jobs 
 where log_user='ABC';

Determine that the user is not associated with any stream replication queues:

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

select apply_name 
  from dba_apply 
 where queue_owner='ABC';  

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

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

After releasing a user from propagation replication, you can drop the user.
You will need to then redefine the replication mechanism.
[ November 17, 2008: Message edited by: Paul Campbell ]