Home » SQL & PL/SQL » SQL & PL/SQL » package going invalid, without modifying dependency (oracle 10g)
package going invalid, without modifying dependency [message #401683] Wed, 06 May 2009 02:57 Go to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Hello,

I am facing a issue, where several packages in database got invalid automatically.

I am sure that, no dependent object has been modified.

Could you please suggest any possible reason.

Thanks and Regards,
Prashant
Re: package going invalid, without modifying dependency [message #401687 is a reply to message #401683] Wed, 06 May 2009 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are wrong in your certainties.
Something changes, maybe privileges (especially public ones).

Regards
Michel
Re: package going invalid, without modifying dependency [message #401690 is a reply to message #401683] Wed, 06 May 2009 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Some dependant object will have been modified or recompiled.

Check the LAST_DDL_TIME from user_objects.
Re: package going invalid, without modifying dependency [message #401822 is a reply to message #401690] Wed, 06 May 2009 10:04 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Thanks for your reply.

However, i am not getting any thing significant in LAST_DDL_TIME.
Could you please suggest any other option, where i can trace the rootcause.

Thanks and Regards,
Prashant
Re: package going invalid, without modifying dependency [message #401851 is a reply to message #401822] Wed, 06 May 2009 11:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Other than setting a package to invalid with DBMS_UTILITY, changing a dependant object is way that objects get set to a status of invalid.

Try running this, and replace 'P_1' with the name of the object you're looking at:
select name
      ,type
      ,uo1.status
      ,uo1.last_ddl_time
      ,referenced_name
      ,referenced_type
      ,uo2.status
      ,uo2.last_ddl_time      
from   user_dependencies ud
      ,user_objects      uo1
      ,user_objects      uo2
where  ud.referenced_name = uo2.object_name      
and    ud.name = uo1.object_name      
connect by prior referenced_name = name
start with name = 'P_1';
Re: package going invalid, without modifying dependency [message #401866 is a reply to message #401683] Wed, 06 May 2009 13:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am facing a issue, where several packages in database got invalid automatically.
Packages do not spontaneously go invalid.
Why is this a problem?
The next time they are invoked, they will be re-compiled before being run.


>I am sure that, no dependent object has been modified.
You may be sure, but you are WRONG in statement above.
Re: package going invalid, without modifying dependency [message #401885 is a reply to message #401866] Wed, 06 May 2009 18:52 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Thanks for the reply.

But still, we are sure that, noboday working on database.

We have seen this scenario 4 times in last month.
And each time we saw 'GES Potential blocker' in alert logs, and there were many locked sessions .

Could lock be the reason of packages getting uncompiled?
Re: package going invalid, without modifying dependency [message #401888 is a reply to message #401683] Wed, 06 May 2009 19:02 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You state you have a problem.
OK, we will stipulate you have a problem.
Please review what you have posted. Just whines & no facts.
Please review what you have not posted.
Post Operating System (OS) name & version for DB server system.
Post Oracle version to 4 decimal place of database s/w.

>I am facing a issue, where several packages in database got invalid automatically.
Provide proof.
Packages do not spontaneously go invalid. Why is this a problem?

>And each time we saw 'GES Potential blocker' in alert logs, and there were many locked sessions .
So you claim. Is CUT & PASTE broken for you?
Is this happening on a RAC environment?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


Post the results from running SQL by JRowbottom (above).

PLEASE answer ALL questions asked of you!

[Updated on: Wed, 06 May 2009 19:12]

Report message to a moderator

Previous Topic: Help with an Index
Next Topic: Help in SQL Query (Merged)
Goto Forum:
  


Current Time: Sat Dec 10 01:27:21 CST 2016

Total time taken to generate the page: 0.13552 seconds