INVALID Objects

All posts relating to Oracle database administration.

Moderator: Tim...

INVALID Objects

Postby me_lucky » Mon Jul 15, 2013 11:23 am

Hi Tim,

Recently i have created a database and imported dump of other database into it.

When i compile all invalid objects, it compiled all and when i check from PL/SQL developer(invalid objects button) it shows me only 3-4 invalid objects.
But when i query select * from dba_objects a where a.status='INVALID', it shows me hundreds of objects out of them most are PUBLIC objects which are synonyms.

I don't understand what is the difference between these two results.

Can u pl explain me.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 168
Joined: Tue Jun 02, 2009 11:40 am

Re: INVALID Objects

Postby Tim... » Mon Jul 15, 2013 1:42 pm

Hi.

I've seen this sort of thing before. The synonyms are marked as invalid, because the object they point to is invalid. You compile the underlying object, which makes it valid, but the synonym is still marked as invalid. The trick is to make sure you recompile PUBLIC as well, then all the public synonyms will be marked as valid.

This should all be done for you if you do the recompile using utlrp.sql.

http://www.oracle-base.com/articles/mis ... bjects.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: INVALID Objects

Postby me_lucky » Tue Jul 16, 2013 5:32 am

Hi Tim,

Thanks for the update. As u said i have recompiled all objects using utlrp.sql now all the objects are VALID.
point is, my application (using Oracle Forms) is configured to use synonyms, we dint faced any issue. If the synonym is invalid it should
give error when we are accessing that. Am i correct ?

Thanks & Regards,
Sabhitha
me_lucky
Senior Member
 
Posts: 168
Joined: Tue Jun 02, 2009 11:40 am

Re: INVALID Objects

Postby Tim... » Tue Jul 16, 2013 7:04 am

Hi.

No. When you attempt to use an invalid object, Oracle attempts to compile it. Only if that compile fails will it result in an error.

The thing you have to be careful of is invalid objects with complex dependencies. If the number and layers of dependencies get sufficiently big, the time it can take to revalidate the objects can be exceptionally large. This is why it makes sense to run something like UTL_RP after any maintenance work, to make sure all invalid objects are revalidated. That is why it is one of the steps after an upgrade. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: INVALID Objects

Postby me_lucky » Wed Jul 17, 2013 5:04 am

Hi Tim,

Thanks For the update.

Now its clear to me. I never use to recompile using utlrp.sql after maintenance activity. We use PL/SQL Developer, we use recompile option from there only.

I never noticed that as we didn't faced any issue with these invalid objects as you said Oracle will try to recompile when we are using it. As our Objects are not having high complexity level.

Thanks a lot for the clear explanation. I'l keep this in mind from next time onwards.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 168
Joined: Tue Jun 02, 2009 11:40 am

Re: INVALID Objects

Postby Tim... » Wed Jul 17, 2013 7:11 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 9 guests

cron