Cannot drop a user [message #677374] |
Sun, 15 September 2019 08:18 |
janakors
Messages: 232 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 #677402 is a reply to message #677384] |
Tue, 17 September 2019 06:36 |
Bill B
Messages: 1971 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 |
Solomon Yakobson
Messages: 3275 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
|
|
|
|