Package de diagnostique pour les undotbs

Les scripts se basent sur l’activité AWR pour faire leur diagnostique, en toute logique leur utilisation est soumise à licence.

set serveroutput on 

DECLARE
tsn VARCHAR2(40);
tss NUMBER(10);
aex BOOLEAN;
unr NUMBER(5);
rgt BOOLEAN;
retval BOOLEAN;
BEGIN
retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
dbms_output.put_line('Undo Tablespace Is: ' || tsn);
dbms_output.put_line('Undo Tablespace Size Is: ' || TO_CHAR(tss));

IF aex THEN
dbms_output.put_line('Undo Autoextend Is Set To: TRUE');
ELSE
dbms_output.put_line('Undo Autoextend Is Set To: FALSE');
END IF;

dbms_output.put_line('Undo Retention Is: ' || TO_CHAR(unr));

IF rgt THEN
dbms_output.put_line('Undo Guarantee Is Set To: TRUE');
ELSE
dbms_output.put_line('Undo Guarantee Is Set To: FALSE');
END IF;
END;
/

Résultat

Undo Tablespace Is: UNDOTBS1
Undo Tablespace Size Is: 32768
Undo Autoextend Is Set To: TRUE
Undo Retention Is: 900
Undo Guarantee Is Set To: FALSE

Estimer le meilleur paramétrage pour « undo_retention » Entre 2 dates

SELECT  dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE)
FROM dual;

En général

SELECT  dbms_undo_adv.best_possible_retention
FROM dual;

Entre 2 snapshots AWR

SELECT dbms_undo_adv.best_possible_retention(10, 20)
FROM dual;