–######################################################################
–# Autor : Rodrigo Mufalani (rodrigo@mufalani.com.br)
–# Data : May 24h, 2010
–# De : Mufalani
–# Para :
–# Motivo : Ajustar retenção de undo e tbs de undo
–######################################################################
–# Obs :
–######################################################################
set serverout on
declare
qry_dem number;
und_ren number;
spc_req number;
und_reo number;
und_tbs varchar2(30);
und_siz1 number;
und_siz2 number;
und_siz3 number;
begin
qry_dem := dbms_undo_adv.longest_query(sysdate-30/24,sysdate);
und_ren := dbms_undo_adv.required_retention(sysdate-30, sysdate);
spc_req := dbms_undo_adv.required_undo_size (und_ren,sysdate-30,sysdate);
select
value into und_reo
from
v$parameter
where
name=’undo_retention’;
select
value into und_tbs
from
v$parameter
where
name=’undo_tablespace’;
select
sum(bytes/1024/1024) into und_siz1
from
dba_data_files
where
tablespace_name=und_tbs;
und_siz2 := round(((604800*spc_req)/und_ren/1024),2);
und_siz3 := round(((86400*spc_req)/und_ren/1024),2);
dbms_output.put_line(‘**********************************’);
dbms_output.put_line(‘ Período análise de 30 dias ‘);
dbms_output.put_line(‘**********************************’);
dbms_output.put_line(‘Tbs Undo atual : ‘||und_siz1||’ Mb’);
dbms_output.put_line(‘Ret Undo atual : ‘||und_reo||’ s’);
dbms_output.put_line(‘Query mais longa :’||qry_dem||’ s’);
dbms_output.put_line(‘**********************************’);
dbms_output.put_line(‘Retenção sugerida :’||und_ren||’ s’);
dbms_output.put_line(‘Alter system set undo_retention=’||und_ren||’ scope=both;’);
dbms_output.put_line(‘Tbs Undo sugerida :’||spc_req||’ Mb’);
dbms_output.put_line(‘**********************************’);
dbms_output.put_line(‘Retenção de 7 dias :604800 s’);
dbms_output.put_line(‘Tbs Undo sugerida :’||und_siz2||’ Gb’);
dbms_output.put_line(‘**********************************’);
dbms_output.put_line(‘Retenção de 1 dia :86400 s’);
dbms_output.put_line(‘Tbs Undo sugerida :’||und_siz3||’ Gb’);
dbms_output.put_line(‘**********************************’);
end;
/