OraFAQ Forum: SQL & PL/SQL » cannot drop a user that is currently connected

Home » SQL & PL/SQL » SQL & PL/SQL » cannot drop a user that is currently connected (oracle10g,linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

cannot drop a user that is currently connected [message #400742]

Wed, 29 April 2009 07:11

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

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400746 is a reply to message #400742]

Wed, 29 April 2009 07:25

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,

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400748 is a reply to message #400746]

Wed, 29 April 2009 07:27

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?

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400751 is a reply to message #400742]

Wed, 29 April 2009 07:30

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

check v$session to see who's connected as that user.

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400752 is a reply to message #400742]

Wed, 29 April 2009 07:30

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.

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400756 is a reply to message #400752]

Wed, 29 April 2009 07:34

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

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400758 is a reply to message #400751]

Wed, 29 April 2009 07:36

cookiemonster
Messages: 13967
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.

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400759 is a reply to message #400742]

Wed, 29 April 2009 07:37

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

Does import completed?

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400760 is a reply to message #400759]

Wed, 29 April 2009 07:38

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?

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400762 is a reply to message #400742]

Wed, 29 April 2009 07:40

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.

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400764 is a reply to message #400762]

Wed, 29 April 2009 07:44

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

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400766 is a reply to message #400764]

Wed, 29 April 2009 07:47

joy_division
Messages: 4963
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.

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400769 is a reply to message #400742]

Wed, 29 April 2009 07:56

cookiemonster
Messages: 13967
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?

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400770 is a reply to message #400769]

Wed, 29 April 2009 08:05

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

yes i have killed all the sessions there displayed in that query

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400772 is a reply to message #400742]

Wed, 29 April 2009 08:08

cookiemonster
Messages: 13967
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.

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400776 is a reply to message #400772]

Wed, 29 April 2009 08:26

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?

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400780 is a reply to message #400776]

Wed, 29 April 2009 08:32

Frank
Messages: 7901
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.

Report message to a moderator

Re: cannot drop a user that is currently connected [message #400783 is a reply to message #400742]

Wed, 29 April 2009 09:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The "internal" Oracle session will remain until after the external Operating System process terminates (or is killed).

Report message to a moderator

Previous Topic:	Latest row within sub-group
Next Topic:	Accessing directory entries in PL/SQL with clustering

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 11 03:59:46 CDT 2025