Invalid Objects Issue

[gdba]

Do you know if any of the stored procedure/function/package is modifying/dropping any table(s) or view(s) at run time that is referenced inside other procedure/functiion/package? If you do have something like that, it can have dominos effect.

Once thing you might want to consider when you have this invalid object issue is to check the last ddl timestamp in dba_objects to see which one has the latest time then you can drill down to the stored programs that reference that object.

My two cents

G-DBA.

[vnktummala]

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?

I am not sure what is going on in your environment. With the symptoms you have posted, for me it looks like a privileges issue.

Go back to previous configuration (with out DBA privilege), take few invalid objects randomly and check whether the application user has necessary privileges on all referenced objects. You can use dba_dependencies view to find the dependencies.

Thanks,

[BeefStu]

If you have TOAD, I beleive there is a feature when looking at packages that
lets you know why the object went Invalid.

As mentioned earlier, the most common cause as to why this happens is
that something CHANGED!!.

To help track down your problem you may want to consider running this
query via CRON every so often. This may give you a clue

set pages 1000
set line 110
set head off
set veri off
set feed off
set trim on
select owner,object_name,object_type,status,last_ddl_time
from dba_objects where status!='VALID'

Good Luck

[PAVB]

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

The solution to what? objects get invalid because of changes in upstream objects. Just compile them or let Oracle compile them as needed.

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

We all know exaclty why as opposite to "what may be" - objects get invalid because of changes in upstream objects.