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;