Register / Login
Win a copy of
Clojure in Action
this week in the
JDBC and Relational Databases
Oracle acts out. Can't drop a user.
posted 7 years ago
Here is what I am trying to do. I just want to drop the damn schema.
C:\>sqlplus /nolog SQL*Plus: Release 220.127.116.11.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.
posted 7 years ago
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:
ORA-00353: log corruption near block 75760 change 5605346 time 01/07/2012 11:41:31
need to drop number of user from database
JDBC Max number of cursors exceeded issue
getting error while droping the user in oracle.
Not able to drop user Oracle10.2.0.3 Ent Ed.