Forum OpenACS Improvement Proposals (TIPs): Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade

Collapse
4: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Dion Sweat on 05/27/04 03:42 AM
Oracle supplies a script to recompile invalid packages and procedures.  You'll usually want to run this after you do a manual database upgrade.  This is so you'll incur the cost of recompiling during the migration process rather than run time.

The sript is located in $ORACLE_HOME/rdbms/admin/utlrp.sql

Collapse
6: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 4)
Posted by Andrew Piskorski on 05/27/04 04:14 PM
Dion, intersting, I did not know that this utlrp.sql script even existed. Looking at the version shipped with Oracle 8.1.7.4, it sounds as if that script will do the job - so that makes 3 entirely different scripts all available to solve this same problem.

However, the comments in utlrp.sql do specifically say that it must be run as Oracle user SYS, which is a very serious limitation. Someone should try it and tell us if that is in fact the case. If it is, it could probably be hacked to fix that, but you could also just use either Trezzo's or Kyte's scripts instead, which don't have that problem.

Btw, of the 3 solutions which should work, the only one I've ever actually used is Trezzo's recompile_all_objects. I found it first, about 4 years ago now, it worked just fine for me, and thus I never tried any of these others.