DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Cannot Change Function definition (session locks)

  1. #1
    Join Date
    Jul 2003
    Posts
    136

    Question Cannot Change Function definition (session hangs)

    Oracle 10g (10.1)
    Windows 2003 server.

    I need to modify a Stored Function but when ever I try to make change and compile (in PLSQL developer etc.), db stops responding and I have to kill the session.

    The Function (GetDecodeValue) is referenced by about 30 views and 1 Function.
    I can execute the Function anytime and but cannot make any changes even when there is no user accessing the Function. There is no lock, I checked.

    Please advice. Thanks
    -D
    Last edited by daljitsb; 03-21-2007 at 11:31 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    stop your applications and users - cant change code if it is being used

  3. #3
    Join Date
    Nov 2001
    Posts
    335
    Does function use any advanced queues packages?
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  4. #4
    Join Date
    Jul 2003
    Posts
    136
    Quote Originally Posted by davey23uk
    stop your applications and users - cant change code if it is being used
    Thanks davey23uk.
    I can change other functions. Why can't I do this particular one.

    Also, are you saying - we have to stop applications to change a function definition even when there is no active session using the function.
    -D

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you have to stop everything because it is being used - yes, a session is touching it

  6. #6
    Join Date
    Jul 2003
    Posts
    136
    Quote Originally Posted by davey23uk
    you have to stop everything because it is being used - yes, a session is touching it
    Its a user function in report schema.
    Why some function changes needs stoping of applications and some don't (with no any active sessions except system sessions).

    Please explain a little more. Thanks

    -Daljit

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    becuase they are being used, its as simple as that

  8. #8
    Join Date
    Jul 2003
    Posts
    136
    Quote Originally Posted by davey23uk
    becuase they are being used, its as simple as that
    How can say it with so much surety that its being used when I am saying there is no open active session.
    -D

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    well there is, cos you cant change it can you - the evidence speaks for itself

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Please open two windows.

    On window #1 try to compile your function and let there hanging

    On window #2 run...

    select /*+ ordered */ w1.sid waiting_sid,
    h1.sid holding_sid,
    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,
    to_name "LOCKED OBJECT"
    from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1, v$object_dependency
    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
    and w.kgllkhdl = to_address
    /

    What do you see?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width