Home » RDBMS Server » Server Administration » cannot recompile package after a hang up compilation (merged) (oracle 10.2.0.3)
cannot recompile package after a hang up compilation (merged) [message #427165] Wed, 21 October 2009 06:54 Go to next message
angelica
Messages: 7
Registered: October 2009
Junior Member
We have a problem with a recompilation package.
4 days ago, we tried to recompile it because we did some changes but during the compilation there was a job execution pending associated with this package and our toad aplication just hang up. No messages, couldn't do anything, so we killed the toad process and turn it on again. We called the oracle administrator to killed all the sesions pending and he did it.
Afterwards we tried to compile it and it did nothing. The administrator said that there wasn't any object blocking it so it should compile but we couldn't manage to recompile it.
We have rename the package and compile it and change the public synonym to point to the new one. Everything worked.
Now we have recompile the old one, because no one is using it. there are no dependencies, but still doing the same thing, hangs up and doesn't recompile.
It seems like something in some part of oracle memory still doing something but it doesn't show it to the administrator.
Do you know how to solve the problem, so we have not to shut down the database to make it work it again?
Is there any bug in this version, that could provoke this?
Thanks for your advice.
Re: cannot recompile package after a hang up compilation (merged) [message #427170 is a reply to message #427165] Wed, 21 October 2009 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Nothing can be say with what you posted.
You have the database and can do some investigation on it, we haven't.

Post what you see.
Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: cannot recompile package after a hang up compilation (merged) [message #427196 is a reply to message #427165] Wed, 21 October 2009 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
A possible cause is that code does DML & does not issue COMMIT or ROLLBACK.
Some days old session invoked code.
After you find & terminate this session, all will be OK.
Re: cannot recompile package after a hang up compilation (merged) [message #427247 is a reply to message #427196] Wed, 21 October 2009 11:39 Go to previous messageGo to next message
angelica
Messages: 7
Registered: October 2009
Junior Member
What do you mean by some days?
The administrator said to me that he had killed all the active sesions and also killed not active sessions.
This happened on friday 24th of october.
Is it possible that it can be a "hidden session" pending since then, and the administrator is not able to see it?
We know that this issue occured again a month ago with another package. At that time, they stopped the database and opened it again. After that, they could recompile it again.
But this time we are trying to find a better solution, because stopped the database is not a good policy in these cases.
Thanks for the advice.
Re: cannot recompile package after a hang up compilation (merged) [message #427251 is a reply to message #427247] Wed, 21 October 2009 11:45 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
October 24th, 2008??? That's when October 24th was on a Friday as it hasn't happened yet in 2009.
Re: cannot recompile package after a hang up compilation (merged) [message #427252 is a reply to message #427165] Wed, 21 October 2009 11:48 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also if you're recompiling packages because you've made changes to the database then I would suggest shutting down the database first to make sure everyone is disconnected is actually a very good idea.
You really don't want to be upgrading the db with people connected.
Re: cannot recompile package after a hang up compilation (merged) [message #427256 is a reply to message #427247] Wed, 21 October 2009 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The administrator said to me that he had killed all the active sesions and also killed not active sessions.

What is Operating System (OS) name & version?

If admin were 100% correct, you would be able to compile code?

select machine, program, trunc(logon_time), count(*) from v$session
where username <> 'oracle'
group by machine, program, trunc(logon_time)
/

post results from SQL above
Re: cannot recompile package after a hang up compilation (merged) [message #427258 is a reply to message #427251] Wed, 21 October 2009 12:03 Go to previous messageGo to next message
angelica
Messages: 7
Registered: October 2009
Junior Member
sorry october 16th.
Re: cannot recompile package after a hang up compilation (merged) [message #427260 is a reply to message #427256] Wed, 21 October 2009 12:09 Go to previous messageGo to next message
angelica
Messages: 7
Registered: October 2009
Junior Member
BlackSwan wrote on Wed, 21 October 2009 19:01
>The administrator said to me that he had killed all the active sesions and also killed not active sessions.

What is Operating System (OS) name & version?
Oracle 10.2.0.4.

If admin were 100% correct, you would be able to compile code?

select machine, program, trunc(logon_time), count(*) from v$session
where username <> 'oracle'
group by machine, program, trunc(logon_time)
/

I would post the result of the query tomorrow because I don't have permission por sys user. I'll ask the administrator.


post results from SQL above

Re: cannot recompile package after a hang up compilation (merged) [message #427262 is a reply to message #427260] Wed, 21 October 2009 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What is Operating System (OS) name & version?
Do you understand the difference between Oracle & Operating System; like Windoze, Unix, etc?


Re: cannot recompile package after a hang up compilation (merged) [message #427265 is a reply to message #427262] Wed, 21 October 2009 12:18 Go to previous messageGo to next message
angelica
Messages: 7
Registered: October 2009
Junior Member
Yes, I do. I thought you were asking for the database, because I didn't know that the operating system has anything to do with the fact that I can't recompile a package that I could recompile a week ago with the same operating system.
Re: cannot recompile package after a hang up compilation (merged) [message #427268 is a reply to message #427265] Wed, 21 October 2009 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If you knew what you were doing, you would not be spending days to find a five minute fix.
If you don't answer my questions, I'll stop trying to answer yours.
Fair enough?
Re: cannot recompile package after a hang up compilation (merged) [message #427335 is a reply to message #427268] Thu, 22 October 2009 02:09 Go to previous messageGo to next message
angelica
Messages: 7
Registered: October 2009
Junior Member
Sorry if I my last answer seem unkind. I didn't want to transmit that. I didn't know that the operating system was important for this matter.
I'll start from the beginning.
I'm a developer and the database administrator told me to try to find an answer to our problem.
Well, I asked him from the version of the operating system and this is it:
AIX 5.3
Re: cannot recompile package after a hang up compilation (merged) [message #427350 is a reply to message #427335] Thu, 22 October 2009 03:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Have a look at v$access - see if any sessions have been using the package you're trying to recompile.

2) When you try to recompile the package, and it hangs, have a look at DBA_BLOCKERS to see which session is blocking you, and also at v$session_wait, which will tell you what the compiling session is waiting for.
Re: cannot recompile package after a hang up compilation (merged) [message #428819 is a reply to message #427350] Fri, 30 October 2009 03:09 Go to previous message
angelica
Messages: 7
Registered: October 2009
Junior Member
We have solved the problem. There were killed sessions that even they were killed they were blocking objects that didn't allow us to recompile de package. We couldn't see that those objects were blocked because we were looking in a higher level.
Previous Topic: Unable to stop the trace files
Next Topic: ORA-19815
Goto Forum:
  


Current Time: Wed Dec 11 22:38:23 CST 2024