Home arrow Oracle arrow Page 3 - Troubleshooting PL/SQL Code

Recompiling Invalid Code - Oracle

In this second part of a nine-part series on managing PL/SQL code, you'll learn how to analyze argument information, recompile invalid code, and more. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Troubleshooting PL/SQL Code
  2. Analyze and modify trigger state through views
  3. Recompiling Invalid Code
  4. Using Native Compilation
By: O'Reilly Media
Rating: starstarstarstarstar / 8
October 25, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Whenever a change is made to a database object, Oracle uses its dependency-related views (such as PUBLIC_DEPENDENCIES) to identify all objects that depend on the changed object. It then marks those dependent objects as INVALID, essentially throwing away any compiled code. This all happens automatically and is one of the clear advantages to compiling programs into the database. The code will then have to be recompiled before it can be executed.

Oracle will automatically attempt to recompile invalid programs as they are called. You can also manually recompile your invalid code, and this section shows how you can do this. Manual recompilation is generally recommended over automatic recompilation, particularly when it involves a production application. Recompilation can take quite a long time; on-demand compilation caused by a user request will generally result in a high level of user frustration.

Recompile individual program units

You can use the ALTER command to recompile a single program. Here are examples of using this DDL command:

  ALTER FUNCTION a_function COMPILE REUSE SETTINGS;
  ALTER PACKAGE my_package COMPILE REUSE SETTINGS;
  ALTER PACKAGE my_package COMPILE SPECIFICATION REUSE SETTINGS;
  ALTER PACKAGE my_package COMPILE BODY REUSE SETTINGS;

You should include the REUSE SETTINGS clause so that other settings for this program (such as compile-time warnings and optimization level) are not inadvertently set to the settings of the current session.

Of course, if you have many invalid objects, you will not want to type ALTER COMPILE commands for each one. You could write a simple query, like the one below, to generate all the ALTER commands:

  SELECT 'ALTER ' || object_type || ' ' || object_name
         || ' COMPILE REUSE SETTINGS;'
   FROM user_objects
  WHERE status = 'INVALID';

The problem with this “bulk” approach is that as you recompile one invalid object, you may cause many others to be marked INVALID. You are much better off relying on Oracle’s own utilities to recompile entire schemas or to use a sophisticated, third-party script created by Solomon Yakobson. These are described in the next section.

Use UTL_RECOMP

Starting with Oracle Database 10g Release 1, the UTL_RECOMP built-in package offers two programs that you can use to recompile any invalid objects in your schema: RECOMP_SERIAL and RECOMP_PARALLEL.

To use UTL_RECOMP, you will need to connect as a SYSDBA account. When running the parallel version, it uses the DBMS_JOB package to queue up the recompile jobs. When this happens, all other jobs in the queue are temporarily disabled to avoid conflicts with the recompilation.

Here is an example of calling the serial version to recompile all invalid objects in the SCOTT schema:

  SQL> CALL utl_recomp.recomp_serial ('SCOTT');

If you have multiple processors, the parallel version may help you complete your recompilations more rapidly. As Oracle notes in its documentation of this package, however, compilation of stored programs results in updates to many catalog structures and is I/O intensive; the resulting speedup is likely to be a function of the speed of your disks.

Here is an example of requesting recompilation of all invalid objects in the SCOTT schema, using up to four simultaneous threads for the recompilation steps:

  SQL> CALL utl_recomp.recomp_parallel ('SCOTT', 4);

Oracle also offers the DBMS_UTILITY.RECOMPILE_SCHEMA program to recompile invalid objects. One advantage of using this program over the UTL_RECOMP alternatives is that you do not need to connect as a SYSDBA account. I recommend, however, that you avoid using DBMS_UTILITY.RECOMPILE_SCHEMA altogether; in some cases, it does not seem to successfully recompile all invalid objects. This may have to do with the order in which it performs the compilations.

If you do not want to have to connect to a SYSDBA account to perform your recompilations, you might consider using a recompile utility written by Solomon Yakobson and found in the recompile.sql file on the book’s web site.



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: