Re: Is it true that view_recompile_jan2008cpu.sql can be run only once during a database's lifetime
From: Jack van Zanen <jack_at_vanzanen.com>
Date: Tue, 18 May 2010 12:33:41 +1000
Message-ID: <AANLkTimqBuZ38oPhBx9M0r3CTdC1405c0O_sdKJXLg5G_at_mail.gmail.com>
This is a section from the script, and it will simply not compile the views after it checks if it was run or not. I would say there would be no harm in running it twice but why would you?
However it will add another entry to the registry$history table which is nothing but pollution. Maybe this is where the statement is comming from.
PATCH_ENTRY NUMBER;
BEGIN
SELECT DISTINCT COUNT(ID) INTO PATCH_ENTRY FROM registry$history where ID = '&&PATCH_NUMBER';
if PATCH_ENTRY > 0
then
dbms_output.put_line ('viewrecomp.sql script is already applied'); RETURN;
end if;
This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
Date: Tue, 18 May 2010 12:33:41 +1000
Message-ID: <AANLkTimqBuZ38oPhBx9M0r3CTdC1405c0O_sdKJXLg5G_at_mail.gmail.com>
This is a section from the script, and it will simply not compile the views after it checks if it was run or not. I would say there would be no harm in running it twice but why would you?
However it will add another entry to the registry$history table which is nothing but pollution. Maybe this is where the statement is comming from.
When in doubt:
It is better to run a very quick select aginst the registry$history table
(select statement is in the patch install guide so you can just cut and
paste it)
Rem =======================================================================Rem To check if script is already applied
Rem =======================================================================DECLARE
PATCH_ENTRY NUMBER;
BEGIN
SELECT DISTINCT COUNT(ID) INTO PATCH_ENTRY FROM registry$history where ID = '&&PATCH_NUMBER';
if PATCH_ENTRY > 0
then
dbms_output.put_line ('viewrecomp.sql script is already applied'); RETURN;
end if;
Jack van Zanen
This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
On 14 May 2010 00:40, Mahadevan, Sundar <Sundar.Mahadevan_at_bmo.com> wrote:
> Hi All,
>
> One of my colleagues said that view_recompile_jan2008cpu.sql should be run
> only once during a database’s lifetime. I tried to search online to find out
> if this was true but no luck. May be I cannot get the right key words. My
> understanding to view_recompile_jan2008cpu.sql is that it is like utlrp.sql
> to recompile all internal views. Please clarify. Thanks in advance.
>
>
>
> --Sundar
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 17 2010 - 21:33:41 CDT