Home » SQL & PL/SQL » SQL & PL/SQL » cannot drop a user that is currently connected (oracle10g,linux)
cannot drop a user that is currently connected [message #400742] Wed, 29 April 2009 07:11 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

i have created a table space and user and executed some data in it.
when i want to drop that user it is giving error message like

cannot drop a user that is currently connected.


conn sys as sysdba
sys

alter tablespace xyz offline;
drop user xyz cascade;
error: . ORA-01940 cannot drop a user that is currently connected

why i have checked all the users not connecting to it
Re: cannot drop a user that is currently connected [message #400746 is a reply to message #400742] Wed, 29 April 2009 07:25 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please check if that user is logged from somewhere. i.e from TOAD,SQL prompt or from other source.

Thanks,
Re: cannot drop a user that is currently connected [message #400748 is a reply to message #400746] Wed, 29 April 2009 07:27 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
no we are all using only one tool i.e sql developer also there are only few members are available with it.
shall i need to stop the tom cat?may it work?
Re: cannot drop a user that is currently connected [message #400751 is a reply to message #400742] Wed, 29 April 2009 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
check v$session to see who's connected as that user.
Re: cannot drop a user that is currently connected [message #400752 is a reply to message #400742] Wed, 29 April 2009 07:30 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
but did u checked that noone is connected with as a xyz user with sql developer?

Quote:
shall i need to stop the tom cat?may it work?
I dont think that will work.
Re: cannot drop a user that is currently connected [message #400756 is a reply to message #400752] Wed, 29 April 2009 07:34 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
what happening actually is when i am using exp command to take back up np problem in doing so.when i want to drop user,tablespace with same name to import dmp file i am facing this error?any thing happening mean while
Re: cannot drop a user that is currently connected [message #400758 is a reply to message #400751] Wed, 29 April 2009 07:36 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 29 April 2009 13:30
check v$session to see who's connected as that user.

Re: cannot drop a user that is currently connected [message #400759 is a reply to message #400742] Wed, 29 April 2009 07:37 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Does import completed?
Re: cannot drop a user that is currently connected [message #400760 is a reply to message #400759] Wed, 29 April 2009 07:38 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah i got message like import terminated succesfully without any errors.bit i am facng only problem with user dropping.i dont know where exactly i am facing this?
Re: cannot drop a user that is currently connected [message #400762 is a reply to message #400742] Wed, 29 April 2009 07:40 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Quote:
cookiemonster wrote on Wed, 29 April 2009 13:30
check v$session to see who's connected as that user.


Re: cannot drop a user that is currently connected [message #400764 is a reply to message #400762] Wed, 29 April 2009 07:44 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
select sid, serial# from v$session where username = 'STRMADMIN';

SID SERIAL#
---------- ----------
268 1268
315 1223

SQL> Alter user strmadmin account lock;

Now kill the connected session.
SQL> alter system kill session '268,1268';
System altered.

SQL> alter system kill session '315,1223';
System altered.

And then drop the user.
SQL> drop user strmadmin cascade;
User dropped.


it happend but immediately after 2 hours
orace idleinstance
oracle database not avaialble message came

some how i manged to came back initially

is there any harm in doing so
Re: cannot drop a user that is currently connected [message #400766 is a reply to message #400764] Wed, 29 April 2009 07:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rajasekhar857 wrote on Wed, 29 April 2009 08:44
immediately after 2 hours



That's an oxymoron if I ever heard one.
Re: cannot drop a user that is currently connected [message #400769 is a reply to message #400742] Wed, 29 April 2009 07:56 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
When I told you to check v$session I assumed you'd use it to check what the sessions were doing rather just blindly killing them.

Quote:

some how i manged to came back initially



what does that mean?

Quote:

is there any harm in doing so



doing what exactly?
Re: cannot drop a user that is currently connected [message #400770 is a reply to message #400769] Wed, 29 April 2009 08:05 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yes i have killed all the sessions there displayed in that query
Re: cannot drop a user that is currently connected [message #400772 is a reply to message #400742] Wed, 29 April 2009 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I worked that out from your previous post. I don't think it was a particularly good idea, but what's done is done.
Next time you end up in a situation like this I strongly recommend you check what the sessions are and what they are doing before you kill them.
Re: cannot drop a user that is currently connected [message #400776 is a reply to message #400772] Wed, 29 April 2009 08:26 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how to know that which session exactly we have to kill?how we can come to know that?
Re: cannot drop a user that is currently connected [message #400780 is a reply to message #400776] Wed, 29 April 2009 08:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajasekhar857 wrote on Wed, 29 April 2009 15:26
how to know that which session exactly we have to kill?how we can come to know that?

Don't.
If you can't tell, you should DEFINITELY stay away from killing other people's sessions.
Re: cannot drop a user that is currently connected [message #400783 is a reply to message #400742] Wed, 29 April 2009 09:19 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
The "internal" Oracle session will remain until after the external Operating System process terminates (or is killed).
Previous Topic: Latest row within sub-group
Next Topic: Accessing directory entries in PL/SQL with clustering
Goto Forum:
  


Current Time: Sat Dec 03 13:51:11 CST 2016

Total time taken to generate the page: 0.12391 seconds