Re: packages becoming invalid at random

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 30 Sep 2009 00:48:17 -0600


Not sure if this is relevant or not, but it sure surprised me...

If you have partitioned tables, and you perform operations like MERGE|RENAME|DROP PARTITION on them, and you also have dependent compile objects like views, packages, procedures, or function referencing these tables, then the following might be relevant...

If your dependent views, packages, procedures, or functions reference the partitioned tables directly (without synonyms), then partition management operations (i.e. MERGE|RENAME|DROP PARTITION) will not invalidate them, at least in 10gR2.  In earlier versions (i.e. v8.0.x, v8.1.x, v9.0.x, v9.2.x), many partition management operations would invalidate and some would not, but Oracle cleaned this up by v10.2.x, at least according to my testing.

However, if your dependent views, packages, procedures, or functions reference the partitioned tables through synonyms, then the synonyms become invalidated by partition management operations.  Of course, this invalidation will also cascade down to the dependent objects that reference the synonym.

So, following Mark's advice, check the DBA_DEPENDENCIES data-dictionary view to see if your compiled objects which are going invalid are referencing partitioned tables.  If so, check to see if they are referencing the table directly, or through a synonym.  If the latter, then check to see if partition management operations are occurring around the time your invalidations are occurring.

If anyone is interested in using a SQL*Plus script I put together long ago to test whether partition management commands cause invalidations in a particular version of Oracle, I'll be glad to share it with them if they request offline from the list.

Hope this helps...
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   =
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt

Powell, Mark D wrote:
If you perform DDL against an object then other objects/code dependent on that object potentially go invalid.  With 11g Oracle has improved the level of granularity for dependency checking but with 10g and below it is pretty much at the object level.  If you alter a table to add, modify, or remove a column then triggers on the table, views that reference the table, and stored code: packages, procedures, and functions that reference the table become invalid.
See the DBA_DEPENDENCIES view and the related documentation

-- Mark D Powell --
HP Enterprise Services
Phone (313) 592-5148


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lyall Barbour
Sent: Monday, September 28, 2009 10:57 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: packages becoming invalid at random

so, we are doing a large Banner (SunGard's front-end application like Oracle Apps, but specific toward higher education) upgrade.  We've got it done on Development Oracle and Test databases.  We've seen weird things going on with the packages in Test going invalid, with what seems like at random. 
Can anyone do a brief list of why packages, procedures, etc will go invalid? like dropping synonyms, or altering tables?  or point me to a doc on that kind of thing?
And then, since this is happening with what seems like at random, if anyone has any info about how to turn on auditing for ALTER PACKAGE or DROP TABLE, etc...  we have auditing on and it shows when users are altered, but i don't know much more about auditing.
Thanks everyone,

An Excellent Credit Score is 750
See Yours in Just 2 Easy Steps!
-- //

Other related posts: