-
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.
-
stop your applications and users - cant change code if it is being used
-
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!
-
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
-
you have to stop everything because it is being used - yes, a session is touching it
-
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
-
becuase they are being used, its as simple as that
-
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
-
well there is, cos you cant change it can you - the evidence speaks for itself
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|