Home » SQL & PL/SQL » SQL & PL/SQL » Cannot drop a user (oracle 11 g r2 RAC of 2 nodes, solaris 10 and ASM)
Cannot drop a user [message #677374] Sun, 15 September 2019 08:18 Go to next message
janakors
Messages: 196
Registered: September 2009
Senior Member
hi,
i am tryin to drop a user but i got this error ORA-01940
then i did following to find the user but

SQL>select sid,serial# from v$session where user like 'JOHN';
no rows selected

now how to drop user John

please guide
Re: Cannot drop a user [message #677380 is a reply to message #677374] Sun, 15 September 2019 08:47 Go to previous messageGo to next message
BlackSwan
Messages: 26604
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below

select USERNAME, sid,serial# from v$session where UPPER(username) like '%JOHN%';
Re: Cannot drop a user [message #677381 is a reply to message #677380] Sun, 15 September 2019 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and post (copy and paste) the command you're trying to execute.

[Updated on: Sun, 15 September 2019 12:31]

Report message to a moderator

Re: Cannot drop a user [message #677382 is a reply to message #677381] Sun, 15 September 2019 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and you should envisage to feedback in your topics above all (but not only) when you say:


janakors wrote on Wed, 11 March 2015 19:06
thank you for your time.let me do it and i will return with results
Re: Cannot drop a user [message #677384 is a reply to message #677374] Sun, 15 September 2019 13:11 Go to previous messageGo to next message
EdStevens
Messages: 1122
Registered: September 2013
Senior Member
janakors wrote on Sun, 15 September 2019 08:18
hi,
i am tryin to drop a user but i got this error ORA-01940
then i did following to find the user but

SQL>select sid,serial# from v$session where user like 'JOHN';
no rows selected

now how to drop user John

please guide
No rows from v$session simply means that "JOHN" does not have a current session running.

What resulted from actually trying to DROP him? Do it in sqlplus and use copy and paste to show us the full command and full result.

Re: Cannot drop a user [message #677402 is a reply to message #677384] Tue, 17 September 2019 06:36 Go to previous messageGo to next message
Bill B
Messages: 1898
Registered: December 2004
Senior Member
as a user with the drop user priviledge the command

DROP USER JOHN CASCADE ;

This will drop the user JOHN and any objects that were created in his schema.
Re: Cannot drop a user [message #677460 is a reply to message #677384] Sat, 21 September 2019 09:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2878
Registered: January 2010
Location: Connecticut, USA
Senior Member
DROP USER ... CASCADE will not do any good if DROP USER raises ORA-01940. OP made very rookie mistake. USER is built-in function returning session user, so

select sid,serial# from v$session where user like 'JOHN';

will never return rows unless session user is JOHN (which in this case is obviously not). OP needs to issue:

select sid,serial# from v$session where userNAME = 'JOHN';

and since it is RAC:

select inst_id,sid,serial# from Gv$session where userNAME = 'JOHN';

Then kill corresponding sessions and then issue

DROP USER JOHN CASCADE;

SY.
Edited: Missed OP has RAC.

[Updated on: Sat, 21 September 2019 09:17]

Report message to a moderator

Re: Cannot drop a user [message #677602 is a reply to message #677460] Mon, 30 September 2019 05:49 Go to previous message
Bill B
Messages: 1898
Registered: December 2004
Senior Member
Nice catch. I assumed that it was pseudo code.
Previous Topic: date usage in SQL query
Next Topic: running total query
Goto Forum:
  


Current Time: Sun Oct 13 17:27:41 CDT 2019