Oracle-L: Re: Online recompilation of SP

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Wed, 22 Jan 2003 05:04:01 -0800
Message-ID: <F001.00537377.20030122050401@fatcity.com>

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: JayK_at_ibsplc.com 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

--

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

Author: Tim Gorman
INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 Wed Jan 22 2003 - 07:04:01 CST