A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Oracle acts out. Can't drop a user.
Joined: Sep 25, 2008
Nov 17, 2008 14:27:00
Here is what I am trying to do. I just want to drop the damn schema.
C:\>sqlplus /nolog SQL*Plus: Release 22.214.171.124.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.
Joined: Oct 06, 2007
Nov 17, 2008 14:44:00
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 ]
I agree. Here's the link:
subject: Oracle acts out. Can't drop a user.
Not able to drop user Oracle10.2.0.3 Ent Ed.
JDBC Max number of cursors exceeded issue
need to drop number of user from database
getting error while droping the user in oracle.
ORA-00353: log corruption near block 75760 change 5605346 time 01/07/2012 11:41:31
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2014