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 126.96.36.199.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.
need to drop number of user from database
Not able to drop user Oracle10.2.0.3 Ent Ed.
ORA-00353: log corruption near block 75760 change 5605346 time 01/07/2012 11:41:31
JDBC Max number of cursors exceeded issue
getting error while droping the user in oracle.
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2014