Home » SQL & PL/SQL » SQL & PL/SQL » package hangs (oracle 10g,win xp)
package hangs [message #323360] Wed, 28 May 2008 05:27 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All

I had a package,while compiling it encountered some errors

now wheni i am trying to compile it again it just hangs,currently the body is showing invalid. but the spec is file but still neither the spec nor the body is getting compiled.This is a production package and need some assistance,kindly help.

It just hangs

Thanks

[Updated on: Wed, 28 May 2008 07:21]

Report message to a moderator

Re: package hangs [message #323364 is a reply to message #323360] Wed, 28 May 2008 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're compiling packages on the live database while people are using it.
That's professional.

My suspicion is that someone is using the package and you are waiting until their session has finished it's transactions.

Re: package hangs [message #323407 is a reply to message #323360] Wed, 28 May 2008 07:24 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I was doing this in offline hours.Not when users are using it.
Re: package hangs [message #323410 is a reply to message #323407] Wed, 28 May 2008 07:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I was doing this in offline hours. Not when users are using it.



Then one easy way to get rid of all possible locks or any other problems in that direction would be to bounce the database.
Re: package hangs [message #323411 is a reply to message #323407] Wed, 28 May 2008 07:34 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I was doing this in offline hours.Not when users are using it.
Do you KNOW it, or do you THINK it was like that?
Re: package hangs [message #323426 is a reply to message #323360] Wed, 28 May 2008 08:07 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I know it,as we are dealing with it frequently.

[Updated on: Wed, 28 May 2008 08:09]

Report message to a moderator

Re: package hangs [message #323427 is a reply to message #323360] Wed, 28 May 2008 08:10 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hey it is working now,this is good for us.
Re: package hangs [message #323554 is a reply to message #323426] Wed, 28 May 2008 15:45 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
swas_fly wrote on Wed, 28 May 2008 15:07
I know it,as we are dealing with it frequently.

Dealing with what? Compiling your code when users work on it? If so - that's a spirit!

So you make changes to a package. Compile it. Oh, no! There's an error! But it is not only this single package that became invalid - dependent procedures are invalid as well! Instead of one invalid package, now you have dozen invalid packages, five procedures and six functions. Users pick up their phones and call you, saying that there's a strange FRM error followed by another ORA error, and they didn't do anything different than usually. Now you start compiling ALL invalid objects. Oh, no! System hangs! It must be because someone is holding something! Who? What? Who knows! <Ctrl+C> won't help. Finally, you end up with SHUTDOWN (possibly ABORT); never mind, users will check what they've done, what has been committed and what rolled back. Tomorrow is another day, the Sun will keep on shining and everything will be just fine.
Re: package hangs [message #323570 is a reply to message #323360] Wed, 28 May 2008 20:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If the database says that the package body is invalid then there are no users using it. Other people could only use the package if its status in the database says valid.

Compiling packages in production is part of production support. Tell me, how does one release a package to production without compiling it in production? Certainly the idea of doing such releases during a low or no use period is a necesity, but then that is what the guy said he was doing.

If the package was taking a long time to compile then I can think of two reasons why I have seen:

1) as was indicated by one of the other posters, there may have been many invalid objects that required re-compilation. Sometimes it can take quite a while to recompile a lot of stuff because of other invalidations.

2) there is a problem of some kind. This can be pretty much everything, but I have seen disk failure and memory failure be culprits to same.

#2 is rare, #1 is common.

Good luck, Kevin
Re: package hangs [message #323572 is a reply to message #323360] Wed, 28 May 2008 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
When folks get older, memory is the 2nd faculty which deteriorates & I don't remember which is the 1st faculty to fail.

From my failing memory, I believe the following is true.
Session #1 invokes a package which does some DML, but does not COMMIT (yet).

Session #2 tries to "compile" this same package & "hangs".
Session #2 remains in limbo until Session #1 issues COMMIT or ROLLBACK.

I have see this more than 1 time where a duhveloper has done this to himself,
while trying to debug his code by having multiple sessions active into the database.

I am too lazy to (re)produce a test case this evening.

If folks want to disagree, they are free to do so.
Re: package hangs [message #323589 is a reply to message #323554] Wed, 28 May 2008 23:17 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Quote:
Dealing with what? Compiling your code when users work on it? If so - that's a spirit!


definitely not,as users don't work in offline hours,so i was doing this when the object wasn't in use.

yes that is the right spirit
Previous Topic: how to find results of groups within groups without using analytic functions such as rank.
Next Topic: Can You tell me any option avilable To Search a File?
Goto Forum:
  


Current Time: Fri Dec 02 16:49:50 CST 2016

Total time taken to generate the page: 0.08889 seconds