OraFAQ Forum: SQL & PL/SQL » Recompiling database objects

Members

Help

Home

Home » SQL & PL/SQL » SQL & PL/SQL » Recompiling database objects (10.2.0.2.0 ,windows)

Show: Today's Messages :: Show Polls :: Message Navigator

E-mail to friend

Recompiling database objects [message #429376]

Tue, 03 November 2009 07:45

Hitman11
Messages: 60
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,

Report message to a moderator

Re: Recompiling database objects [message #429378 is a reply to message #429376]

Tue, 03 November 2009 07:47

joy_division
Messages: 2886
Registered: February 2005
Location: NY

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

Hitman11
Messages: 60
Registered: October 2009
Location: norway

Member

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

Regards,

Report message to a moderator

Re: Recompiling database objects [message #429381 is a reply to message #429380]

Tue, 03 November 2009 07:53

joy_division
Messages: 2886
Registered: February 2005
Location: NY

Senior Member

Do you really need another reason?

Report message to a moderator

Re: Recompiling database objects [message #429382 is a reply to message #429381]

Tue, 03 November 2009 07:56

Hitman11
Messages: 60
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,

Report message to a moderator

Re: Recompiling database objects [message #429383 is a reply to message #429382]

Tue, 03 November 2009 08:02

Its_me_ved
Messages: 127
Registered: October 2009
Location: India

Senior Member

You should read this

Report message to a moderator

Re: Recompiling database objects [message #429386 is a reply to message #429376]

Tue, 03 November 2009 08:07

cookiemonster
Messages: 1438
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.

Report message to a moderator

Re: Recompiling database objects [message #429395 is a reply to message #429383]

Tue, 03 November 2009 08:38

Hitman11
Messages: 60
Registered: October 2009
Location: norway

Member

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

Regards,

Report message to a moderator

Re: Recompiling database objects [message #429410 is a reply to message #429395]

Tue, 03 November 2009 10:28

Kevin Meade
Messages: 1050
Registered: December 1999

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

Report message to a moderator

Re: Recompiling database objects [message #429411 is a reply to message #429410]

Tue, 03 November 2009 10:38

cookiemonster
Messages: 1438
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.

Report message to a moderator

Re: Recompiling database objects [message #429414 is a reply to message #429411]

Tue, 03 November 2009 11:01

Kevin Meade
Messages: 1050
Registered: December 1999

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

Report message to a moderator

Re: Recompiling database objects [message #429426 is a reply to message #429414]

Tue, 03 November 2009 13:11

Its_me_ved
Messages: 127
Registered: October 2009
Location: India

Senior Member

Thanks to cookie and Kevin Sir for providing more details on it.

Regards,
Ved

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 1 20:32:27 CST 2009

Total time taken to generate the page: 1.24668 seconds

.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.