Home » SQL & PL/SQL » SQL & PL/SQL » Recompiling database objects (10.2.0.2.0 ,windows)
Recompiling database objects [message #429376] Tue, 03 November 2009 07:45 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Hi all,

I would like to ask about invalid objects.I know something about it, like if the dependent object
becomes invalid ,we need to compile them to make valid.

Here my question is, why we are making the objects VALID again and what if objects remain
INVALID in database?

Will it cause harm to database ?


Regards,


Re: Recompiling database objects [message #429378 is a reply to message #429376] Tue, 03 November 2009 07:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Hitman11 wrote on Tue, 03 November 2009 08:45

Here my question is, why we are making the objects VALID again and what if objects remain
INVALID in database?


Do I really believe what I am seeing asked here? I think I can ask someone who knows nothing about a database to answer this question.

"Why do I have to use the valid gasoline designed for my car? What harm will it do if I use invalid gasoline?"

I'm looking for a better analogy.

[Updated on: Tue, 03 November 2009 07:52]

Report message to a moderator

Re: Recompiling database objects [message #429380 is a reply to message #429378] Tue, 03 November 2009 07:52 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

I know something like database will not function properly .Is this the reason or something else ??


Regards,
Re: Recompiling database objects [message #429381 is a reply to message #429380] Tue, 03 November 2009 07:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Do you really need another reason?
Re: Recompiling database objects [message #429382 is a reply to message #429381] Tue, 03 November 2009 07:56 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Yes, if it is a VALID one.Im just asking this question whether the answer i provided is the correct wrong?


Regards,

Re: Recompiling database objects [message #429383 is a reply to message #429382] Tue, 03 November 2009 08:02 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
You should read this


Re: Recompiling database objects [message #429386 is a reply to message #429376] Tue, 03 November 2009 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
When objects go invalid in the database you recompile them to make sure that they can be recompiled.

While INVALID status doesn't necessarily mean there is something wrong with them it certainly doesn't mean there is nothing wrong.

Say you drop a column from a table. Any code that references that column will be marked as INVALID. It can't become VALID again until it's changed.

The only way you can be sure that a change to the DB hasn't broken anything is to make sure everything compiles afterwards.
Re: Recompiling database objects [message #429395 is a reply to message #429383] Tue, 03 November 2009 08:38 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Thanks for the useful link and the explanation provided by Cookie.

Regards,
Re: Recompiling database objects [message #429410 is a reply to message #429395] Tue, 03 November 2009 10:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
please allow me to clarify cookiemoster's notes with something more.

Quote:
Say you drop a column from a table. Any code that references that column will be marked as INVALID. It can't become VALID again until it's changed.


This is techincally true yet also incorrect.

If you drop a column from a table, any object that references the table will become invalid; even objects that do not reference the column that was dropped. This fact gives us insight into the makings of invalidation. Dependencies are almost without exception are based on the affected object(s), not object components.

It should also be noted that invalidation is transitive (or is it commutative?):

if a depends upon b and b depends upon c then when c goes invalid, a goes invalid also, not just b

But all of this is well desribed in the link provided earlier by Its_me_ved.

As a quick summary there three basic things to keep in mind as regards valid and invalid objects:

1) dependencies between objects are made at the object level
2) invalidation is transitive (or is it commutative?), and it often leads to what is called cascading invalidation
3) recompilation to make things valid again is continuosly being done by oracle behind the scenes so you do not normally have to do any recompilation manually. This makes me wonder why you think you need to do it manually because normally you don't.

But read the link you were given. All this is explained there.

Good luck, Kevin
Re: Recompiling database objects [message #429411 is a reply to message #429410] Tue, 03 November 2009 10:38 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Tue, 03 November 2009 16:28


Quote:
Say you drop a column from a table. Any code that references that column will be marked as INVALID. It can't become VALID again until it's changed.


This is techincally true yet also incorrect.

If you drop a column from a table, any object that references the table will become invalid; even objects that do not reference the column that was dropped. This fact gives us insight into the makings of invalidation. Dependencies are almost without exception are based on the affected object(s), not object components.



Nah it's completely true. I just didn't go far enough Smile
Mainly I was highlighting a case in which INVALID really is a problem.

It's also worth noting that in 11g you get column level dependencies.

Kevin Meade wrote on Tue, 03 November 2009 16:28

3) recompilation to make things valid again is continuosly being done by oracle behind the scenes so you do not normally have to do any recompilation manually. This makes me wonder why you think you need to do it manually because normally you don't.


Don't know about that. If you're changing db objects you probably should compile everything just to be sure. I always do that whenever I upgrade a production system (admittedly I don't usually bother on dev or test). It's a good safety check.
Re: Recompiling database objects [message #429414 is a reply to message #429411] Tue, 03 November 2009 11:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks cookiemonster.

Please post a link to 11g feature related to dependencies. All I have seen (and I have not looked that closely) is fine-grained dependency tracking, and that seems to be limited to the special case of adding new items for example: when you add a column to a table, or a new cursor to a package specification. I would enjoy reading about the opposite and about the full details. I would test the drop of a column but I do not yet have access to an 11g version.

As regards recompiling invalid objects. Oracle attempts a recompile when ever an invalid object is referenced. Similarly to cascading invalidation, this attempt to recompile can result in a cacasding recompile and thus a cascading revalidation. We often see this in test systems as manifested by an unusually long wait time when executing a package call after changing something. I use "recompile" here in a loose sense as in order to make certain object types valid, a true recomple is not actually necessary (exp. synonyms). It is interesting that you manually recompile, I never bother. I believe manual recompilation is a throwback to earlier versions. Still, I leave open the possibility that it might be necessary by noting "normally you never have to recompile".

Kevin
Re: Recompiling database objects [message #429426 is a reply to message #429414] Tue, 03 November 2009 13:11 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks to cookie and Kevin Sir for providing more details on it.

Regards,
Ved
Previous Topic: trying to create a new column a specific position in already existing table
Next Topic: MAX() resulting in random -2112 errors
Goto Forum:
  


Current Time: Fri Dec 02 12:22:18 CST 2016

Total time taken to generate the page: 0.07839 seconds