Go up

Next thread:	auto update ftp script
Prev thread:	statspack report help

Message	Score	Author	Date
Hi, To create a oracle user, I used the followi......		Ranjeesh Ramakrishnan	Dec 27, 2004
Hi, The user is still connected so you can't dr......		Michel Cadot	Dec 27, 2004
Hi, Thanks for the response. But the V$sessi......		Ranjeesh Ramakrishnan	Dec 28, 2004
Hi, Check if there is any job belonging to this......		Michel Cadot	Dec 28, 2004
No, There are no jobs for that user :(...		Ranjeesh Ramakrishnan	Dec 28, 2004
Hi, Is there any queue? or Streams? select q......		Michel Cadot	Dec 28, 2004
Thanks michel .. Atlast we managed to restart t......		Ranjeesh Ramakrishnan	Dec 29, 2004
Ranjeesh, Never forget to tell us how the probl......		Yong Huang	Jan 03, 2005
Although the session does not show in v$session, t......		Scott Aldrich	Nov 20, 2009

Follow up by mail

Click here

---

Subject:	ORA-01940: cannot drop a user that is currently connected
Author:	Ranjeesh Ramakrishnan, India
Date:	Dec 27, 2004, 2076 days ago
Os info:	IBM AIX Unix
Oracle info:	9i
Error info:	SQL> drop user idncnadm; drop user idncnadm * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected SQL>
Message:	Hi, To create a oracle user, I used the following syntax CREATE USER IDNCNADM IDENTIFIED BY XYZ DEFAULT TABLESPACE REGISTRY_DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; GRANT CONNECT TO IDNCNADM; GRANT RESOURCE TO IDNCNADM; GRANT REGISTRY_IDNCN_R TO IDNCNADM; later while trying to drop, I was getting the following error message SQL> drop user idncnadm; drop user idncnadm * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected SQL> As I cannot restart the database , I killed all the relevant sessions from OEM few days back itself . Any thoughts ? with thanks in advance Ranjeesh
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Michel Cadot, France
Date:	Dec 27, 2004, 2076 days ago
Score:
Message:	Hi, The user is still connected so you can't drop it. Use the following query to see the user session: select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = '<your user>' and p.addr (+) = s.paddr / Kill the session that has not been already kill with: alter system kill session '<sid>,<serial#>'; If all the session are noted 'KILLED' then kill the Unix process (column spid) with "kill -9". Then after a while the session will be removed from v$session and you will be able to drop the user. Regards Michel
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Ranjeesh Ramakrishnan, India
Date:	Dec 28, 2004, 2075 days ago
Message:	Hi, Thanks for the response. But the V$session is not showing any records for that user. There are records for other users. Any other alternative ways? regards
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Michel Cadot, France
Date:	Dec 28, 2004, 2075 days ago
Score:
Message:	Hi, Check if there is any job belonging to this user: select job from dba_jobs where log_user='<your user>'; Regards Michel
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Ranjeesh Ramakrishnan, India
Date:	Dec 28, 2004, 2075 days ago
Score:
Message:	No, There are no jobs for that user :(
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Michel Cadot, France
Date:	Dec 28, 2004, 2075 days ago
Score:
Message:	Hi, Is there any queue? or Streams? select queue_table, qid from dba_queues where owner='<your user>'; select capture_name, queue_name, from dba_capture where queue_owner='<your user>'; select propagation_name from dba_propagation where source_queue_owner='<your user>' or destination_queue_owner='<your user>'; select apply_name from dba_apply where queue_owner='<your user>'; Do you ever use Streams with this user? Regards Michel
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Ranjeesh Ramakrishnan, India
Date:	Dec 29, 2004, 2074 days ago
Message:	Thanks michel .. Atlast we managed to restart the machine :)) . so now it is fine Regards Ranjeesh
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Yong Huang, United States
Date:	Jan 03, 2005, 2069 days ago
Message:	Ranjeesh, Never forget to tell us how the problem was solved. Just saying "we managed to restart the machine" is not enough. Yong Huang
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: ORA-01940: cannot drop a user that is currently connected
Author:	Scott Aldrich, United States
Date:	Nov 20, 2009, 286 days ago
Message:	Although the session does not show in v$session, there is likely still a session connected. Verify all applications that connect to the database are indeed disabled/turned off. I suspect you find an application still connected, stop that application and you should be able to drop the user without restarting.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here