Re: Online recompilation of SP

[Tim Gorman]

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? ----- Original Message ----- From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 21, 2003 11:28 PM Subject: Online recompilation of SP 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 Jai