Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Invalid packages not being recompiled by Oracle

RE: Invalid packages not being recompiled by Oracle

From: Troiano, Paul (CAP, GEFA) <Paul.Troiano_at_gecapital.com>
Date: Thu, 28 Jun 2001 13:41:14 -0700
Message-ID: <F001.0033CB36.20010628134120@fatcity.com>

Thanks Lisa and others,  

The dbms_utility.compile_schema will work. It is a bit annoying though as this involves six schemas and hundreds of packages to be recompiled. Fortunatly I do keep control and there are no circular dependencies in the packages. I am sending out an order of dependency among the schemas to the appropriate people. The recursive error seems to have been fixed when I patched to 8.0.5.2.1. Has anyone seen it in 8.1.7?  

It seems to be only for one package that this happens. It is larger than the rest at 6000 lines and 250 KB ????  

Modifying the application to execute again isn't really feasible in this case. There are dozens of packages that refer to this packages. Each one would have to be modified and fully regression tested.  

As far as standard behavior, Oracle is supposed to (and does in all other cases except for this package) automatically recompile any package flagged as invalid at execution time. It should only raise an error when the package in question can not be recompiled. In this case, the user can 'alter package package_name compile' and all is fixed, provided the 'user' is privileged to do so. Obviously, in production there aren't many who can do this.  

Any thoughts as to how or what could cause this normal behavior to fail?  

-----Original Message-----
Sent: Thursday, June 28, 2001 1:20 PM
To: Multiple recipients of list ORACLE-L

HI Paul,

By chance can you use dbms_utility.compile_Schema after recompiling? Are you using it already? I know it doesn't answer your quesiton but this package is suppossed to follow the dependencies, no matter how odd they are (what you are describing below is pretty weird).

HTH
Lisa Koivu
Database Bored Administrator
Ft. Lauderdale, FL, USA

        -----Original Message-----
Sent: Thursday, June 28, 2001 2:56 PM To: Multiple recipients of list ORACLE-L

        We have one package A that refers to package B. If package B's body and
specification are both recompiled by user 1, package A is correctly marked as invalid. Another user, user 2, then attempts to execute package A and gets the following error stack:

        User 2 then issues the statement 'alter package A compile;' It compiles
successfully and user 2 can now execute the package.

        Why does Oracle not automatically recompile package A as it should? Since
user 2 was able to recompile the package in its existing state, Oracle should not have failed at doing so. User 2's session instantiation of package A (as well as all of their other instantiated packages) should have been lost when package A was invalidated.

        I understand that two possible options are: (1) Flush the shared pool after
recompiling; and (2) Manually recompile all dependent packages (such as A). The first seems like overkill and will cause performance issues. The second,

with many dependencies involved, is not an option.

        We need to consistently be able to recompile a package that other packages
are dependent upon without a user recieving the above error stack in addition to not impacting performance or without having to recompile all the

dependent packages.

        Thanks in advance,

        --
Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com>
--

Author: Troiano, Paul (CAP, GEFA)
  INET: Paul.Troiano_at_gecapital.com

        Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Troiano, Paul (CAP, GEFA)
  INET: Paul.Troiano_at_gecapital.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jun 28 2001 - 15:41:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US