Home » SQL & PL/SQL » SQL & PL/SQL » Lock on Drop User
Lock on Drop User [message #192690] Wed, 13 September 2006 05:01 Go to next message
melanj
Messages: 2
Registered: September 2006
Location: Makati, Philippines
Junior Member

Each time i execute this function:

FUNCTION DROP_USER(O_error_message IN OUT VARCHAR2,
I_user IN VARCHAR2)
RETURN BOOLEAN IS

L_statement VARCHAR2(255);

BEGIN

L_statement := 'drop user "'||I_user||'" cascade';
EXECUTE IMMEDIATE L_statement;
return TRUE;

EXCEPTION
when OTHERS then
return FALSE;

END DROP_USER;


I always receive a ORA-4021 locking error:


when i execute this query:

select /*+ ordered */ w1.sid waiting_session, h1.sid holding_session, w.kgllktype lock_or_pin, w.kgllkhdl address, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held, decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr and h.kgllkuse = h1.saddr



the result looks like this:

WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN ADDRESS MODE_HELD MODE_REQUESTED
61 61 Pin C000000039D13B78 Share Exclusive


Do you have any idea why the result contain the same value for waiting and holding session?


I have these user info
LOGIN ID: USR_ME
USER TO DROP: USR_RJ


Thanks!
Re: Lock on Drop User [message #192694 is a reply to message #192690] Wed, 13 September 2006 05:06 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
not sure but seems to be, some kind of deadlock.
Please read some on deadlock detection, and Handeling.
Re: Lock on Drop User [message #192923 is a reply to message #192694] Thu, 14 September 2006 04:36 Go to previous messageGo to next message
melanj
Messages: 2
Registered: September 2006
Location: Makati, Philippines
Junior Member



could it be a deadlock?

but i haven't recieved ORA-00060 error
and i only use 1 session.
Re: Lock on Drop User [message #208112 is a reply to message #192690] Fri, 08 December 2006 04:09 Go to previous message
bochard
Messages: 1
Registered: December 2006
Junior Member
Hi,

anyone that can help here? I'm also experiencing this error....

Thanks!
-bochard
Previous Topic: How to get data from another table (if its there)
Next Topic: plz give sol of this query
Goto Forum:
  


Current Time: Fri Dec 09 05:44:13 CST 2016

Total time taken to generate the page: 0.16121 seconds