Invalid Objects Issue

[iris]

Hi, Iam facing frequent problem of getting DB objects Invalid. The database is Oracle 10g RAC on Solaris OS. The application is under development stage and the schema is having all the necessary previleges. Executing utlrp.sql temporarily resolves this problem.


The parameter, PLSQL_CODE_TYPE is set to INTERPRETED.

My question is, Can I set this parameter to NATIVE in RAC environment. I came to know that by setting this parameter to NATIVE resolves the Invalid objects issue.

If this is true, what are the other parameters to be set. Like

PLSQL_NATIVE_LIBRARY_DIR, does the directory path should be on shared storage as this is RAC environment?


thanks,

[iris]

kind help is appreciated.

[vnktummala]

The parameter, PLSQL_CODE_TYPE is set to INTERPRETED.

My question is, Can I set this parameter to NATIVE in RAC environment. I came to know that by setting this parameter to NATIVE resolves the Invalid objects issue.

The above parameter is to decide compilation mode. I am sure it is nothing to do with object status.

Oracle changes the object status to invalid if the referenced object(s) are deleted, altered, moved, or any change in the privileges. However, if the conditions are met, Oracle automatically compile the invalid object(s) during next execution time.

Please list out what objects are getting invalid frequently. As it is the development environment there is a good chance that developers are playing with objects which changes the object status to invalid.

Thanks,

[iris]

Hi, I agree that the parameter is for compilation mode. But the developers are complaining that the same application with same privileges on other clients does not create this problem.

The objects getting invalid are: Procedures, Functions, Views and Packages.

Moreover When i have given schema the DBA privilege this problem is resolved to maximum extent, I can say no invalid objects just 3 to 6 invalid objects. Previously the number was going to >600.


thanks,

[PAVB]

Who owns those 900 invalid objects?

[iris]

Hi, the objects are owned by the same schema. I could not understand I have given the privileges as requested by the application developers, which produces frequent Invalid objects and same on giving DBA privileges then executing utlrp.sql the objects becomes valid.

So my question is, Objects updates makes them invalid but given DBA privilege on same schema all works fine, Iam confused?


Kind help is appreciated.

thanks,

[PAVB]

Hi, the objects are owned by the same schema. I could not understand I have given the privileges as requested by the application developers, which produces frequent Invalid objects and same on giving DBA privileges then executing utlrp.sql the objects becomes valid.

So my question is, Objects updates makes them invalid but given DBA privilege on same schema all works fine, Iam confused?

1- Objects are getting invalidated 'cause of developers are working. Each time developer changes an object all dependant objects go invalid.
2- Objects get valid after running utlrp.sql 'cause that's what utlrp.sql does - compile invalid objects.
3- What's the problem about having invalid objects in a development schema? Oracle will compile them as needed.

[iris]

I agree objects gets invalid upon update in test environment, but the concern is when given DBA privilege in same environment no invalid objects occurs.

My question is: Does Objects don't get Invalid on giving DBA privilege?


Kind help is appreciated.


thanks,

[vnktummala]

I agree objects gets invalid upon update in test environment, but the concern is when given DBA privilege in same environment no invalid objects occurs.

My question is: Does Objects don't get Invalid on giving DBA privilege?

No, object state is not related with DBA privilege.

Read this to know more about object dependencies.

Thanks,

[iris]

Hi, appreciate your help.

Gone through the document, but did not find the solution.

Can you please help me in finding what may be the root cause for this?


thanks,