Re: packages becoming invalid at random

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Thu, 1 Oct 2009 14:32:23 +0100
Message-ID: <OF90B8E7DE.471407A7-ON80257642.004A3542-80257642.004A607C_at_ons.gsi.gov.uk>



There is a patch for the synonym invalidation issue available for 10.2.0.3. and 10.2.0.4 - I think the number was 6725634

Cheers,

Ian

|---------+----------------------------->

| | tim_at_evdbt.com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 30/09/2009 07:48 |
| | Please respond to |
| | tim |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | cc: | | Subject: Re: packages becoming invalid at random | >--------------------------------------------------------------------------------------------------------------|

Lyall,

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   = http://www.EvDBT.com/
email     = Tim_at_EvDBT.com
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_at_freelists.org [
            mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyall
            Barbour
            Sent: Monday, September 28, 2009 10:57 AM
            To: oracle-l_at_freelists.org
            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
            10.2.0.4 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,
            Lyall

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

This email was received from the INTERNET and scanned by the Government Secure Intranet anti-virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In case of problems, please call your organisation’s IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
-- http://www.freelists.org/webpage/oracle-l

For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk


Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications


Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics


The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. i0zX+n{+i^ Received on Thu Oct 01 2009 - 08:32:23 CDT

Original text of this message