Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Online recompilation of SP

Re: Online recompilation of SP

From: Tim Gorman <>
Date: Wed, 22 Jan 2003 05:04:01 -0800
Message-ID: <>

Short answer is no. Can't think of an environment where compiled objects can be recreated without quiescence, so its not just Oracle.

You should use PL/SQL packages instead of stored procedures or functions so that the "body" of the package (containing code) can be disassociated and recompiled separately from the "header" or "public specification" section of the package. This way, you can recompile just the package body without cascading invalidations (due to the recompile) throughout other referencing stored objects (i.e. views, triggers, other packages, procedures, functions). Only the header references the body, and it is not invalidated when the body is recompiled. All other stored objects reference the header only, so they will be invalidated only if the header is recompiled, not the body...

In a compiled environment, the only thing you can do is minimize the period of quiescence (i.e. downtime) using redundancy. Two schemas in the same database, two databases within the same environment, etc...

Anyone know of a better way?

  Dear all,

  Is there any means of changing the body of a SP and   recompiling it while there "could" possibly be some   sessions accessing it. We have such a req in our appln   wherein the recreation shouldn't affect ongoing applns   that might access the same SP.

  T I A.

  Best Regards


Please see the official ORACLE-L FAQ:

Author: Tim Gorman

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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 Wed Jan 22 2003 - 07:04:01 CST

Original text of this message