Oracle DBMS_UNDO_ADV
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Undo advisor gives users recommendation on setting undo retention and sizing the undo tablespace.
AUTHID CURRENT_USER
Data Types CREATE OR REPLACE TYPE sys.dbms_uadv_arr AS VARRAY(100) OF NUMBER;
/
Dependencies
CDB_TABLESPACES DBMS_STANDARD PLITBLM
DBA_TABLESPACES DBMS_SYS_ERROR PRVTEMX_ADMIN
DBMS_ADVISOR DBMS_UADV_ARR PRVT_UADV
DBMS_SQL DBMS_UNDOADV_LIB UTL_LMS
Documented No
First Available 10.1
Overload Definitions
Error Code Reason
Overload 1 Subprogram is based on historical information in memory or in SWRF from start time to end time
Overload 2 Subprogram is based on historical information in memory or in SWRF from sysdate-7 to sysdate
Overload 3 Subprogram is based on historical information in SWRF from snapid s1 to snapid s2 (AWR)
Security Model Owned by SYS with EXECUTE granted to the DBA role.
Source {ORACLE_HOME}/rdbms/admin/dbmsuadv.sql
Subprograms
 
BEST_POSSIBLE_RETENTION
Returns best possible value for the  init.ora parameter undo_retention in order to maxmize the usage of current undo tablespace based on historical information of given period
Overload 1
dbms_undo_adv.best_possible_retention(
starttime IN DATE,
endtime   IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.BEST_POSSIBLE_RETENTION(SYSDATE-1/24,SYSDATE)
-----------------------------------------------------------
                                                  227584832
Overload 2 dbms_undo_adv.best_possible_retention RETURN NUMBER;
SELECT dbms_undo_adv.best_possible_retention
FROM dual;

BEST_POSSIBLE_RETENTION
-----------------------
                 907529
Overload 3 dbms_undo_adv.best_possible_retention(
s1 IN NUMBER,
s2 IN NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

Instance     DB Name        Snap Id   Snap Started        Level
------------ ------------ --------- --------------------- -----
test21db     TEST21DB          3295     28 APR 2021 00:58     1
                               3296     28 APR 2021 01:58     1
                               3297     28 APR 2021 02:58     1
...
                               3507     06 MAY 2021 18:58     1
                               3508     06 MAY 2021 19:58     1
                               3509     06 MAY 2021 20:58     1


SELECT dbms_undo_adv.best_possible_retention(3485, 3509)
FROM dual;

DBMS_UNDO_ADV.BEST_POSSIBLE_RETENTION(3485,3509)
------------------------------------------------
                                        14056602
 
LONGEST_QUERY
Returns the length of the longest query in seconds (between the start and end time)

Overload 1
dbms_undo_adv.longest_query(
starttime IN DATE,
endtime   IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.longest_query(SYSDATE-2, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
                                         19662
Overload 2 dbms_undo_adv.longest_query RETURN NUMBER;
SELECT dbms_undo_adv.longest_query
FROM dual;

LONGEST_QUERY
-------------
         1921
Overload 3 dbms_undo_adv.longest_query(
s1 IN NUMBER,
s2 IN NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT dbms_undo_adv.longest_query(3485, 3509)
FROM dual;

DBMS_UNDO_ADV.LONGEST_QUERY(3485,3509)
--------------------------------------
                                  1921
 
RBU_MIGRATION
Returns the required undo tablespace size if users want to migrate from rbu to aum. This function should be called only when undo management is manual
Overload 1
dbms_undo_adv.rbu_migration(
starttime IN DATE,
endtime   IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.rbu_migration(SYSDATE-1/24, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.RBU_MIGRATION(SYSDATE-1/24,SYSDATE)
-------------------------------------------------
                                               27
Overload 2 dbms_undo_adv.rbu_migration RETURN NUMBER;
SELECT dbms_undo_adv.rbu_migration
FROM dual;

RBU_MIGRATION
-------------
           27
 
REQUIRED_RETENTION
Returns the required value for init.ora parameter undo_retention in order to prevent snap-shot-too-old error based on historical information of given period

Overload 1
dbms_undo_adv.required_retention(
starttime IN DATE,
endtime   IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.required_retention(SYSDATE-30, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-30,SYSDATE)
----------------------------------------------------
                                                1921


SELECT dbms_undo_adv.required_retention(SYSDATE-2000/1440, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-2000/1440,SYSDATE)
-----------------------------------------------------------
                                                        253
Overload 2 dbms_undo_adv.required_retention RETURN NUMBER;
SELECT dbms_undo_adv.required_retention
FROM dual;
Overload 3 dbms_undo_adv.required_retention(
s1 IN NUMBER,
s2 IN NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT dbms_undo_adv.required_retention(3485, 3509)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_RETENTION(3485,3509)
-------------------------------------------
                                       1921
 
REQUIRED_UNDO_SIZE
Returns the required undo tablespace size, in MB, to support undo retention based on undo stats
Overload 1
dbms_undo_adv.required_undo_size(
retention IN NUMBER,
starttime IN DATE,
endtime   IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.required_undo_size(900, SYSDATE-30, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(900,SYSDATE-30,SYSDATE)
--------------------------------------------------------
                                                      96
Returns the required undo tablespace size, in MB, to support undo retention based on undo stats

Overload 2
dbms_undo_adv.required_undo_size(retention IN NUMBER)
RETURN NUMBER;
SELECT dbms_undo_adv.required_undo_size(20)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(20)
------------------------------------
                                  10


SELECT dbms_undo_adv.required_undo_size(1440)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(1440)
--------------------------------------
                                   100
Returns the required undo tablespace size, in MB, to support undo retention based on undo stats

Overload 3
dbms_undo_adv.required_undo_size(
retention IN NUMBER,
s1        IN NUMBER,
s2        IN NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT dbms_undo_adv.required_undo_size(1440, 3485, 3509)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(1440,3485,3509)
------------------------------------------------
                                              73
Returns the required undo tablespace size given undo retention value

Overload 4
dbms_undo_adv.required_undo_size(
retention IN     sys.dbms_uadv_arr,
utbsize   IN OUT sys.dbms_uadv_arr,
starttime IN     DATE,
endtime   IN     DATE)
RETURN NUMBER;
set serveroutput on

DECLARE
 uar sys.dbms_uadv_arr;
 utb sys.dbms_uadv_arr;
 ret NUMBER;
BEGIN
  uar := sys.dbms_uadv_arr(900,1800,3600);
  utb := sys.dbms_uadv_arr();
  ret := dbms_undo_adv.required_undo_size(uar, utb, SYSDATE-2, SYSDATE);
  dbms_output.put_line(ret);
  dbms_output.put_line(utb(1));
  dbms_output.put_line(utb(2));
  dbms_output.put_line(utb(3));
END;
/
1
42
42
42

PL/SQL procedure successfully completed.
Returns the required undo tablespace size given undo retention value

Overload 5
dbms_undo_adv.required_undo_size(
retention IN     sys.dbms_uadv_arr,
utbsize   IN OUT sys.dbms_uadv_arr)
RETURN NUMBER;
set serveroutput on

DECLARE
 uar sys.dbms_uadv_arr;
 utb sys.dbms_uadv_arr;
 ret NUMBER;
BEGIN
  uar := sys.dbms_uadv_arr(900,1800,3600);
  utb := sys.dbms_uadv_arr();
  ret := dbms_undo_adv.required_undo_size(uar, utb);
  dbms_output.put_line(ret);
  dbms_output.put_line(utb(1));
  dbms_output.put_line(utb(2));
  dbms_output.put_line(utb(3));
END;
/
1
96
100
122

PL/SQL procedure successfully completed.
Returns the required undo tablespace size given undo retention value

Overload 6
dbms_undo_adv.required_undo_size(
retention IN     sys.dbms_uadv_arr,
utbsize   IN OUT sys.dbms_uadv_arr,
s1        IN     NUMBER,
s2        IN     NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set serveroutput on

DECLARE
 uar sys.dbms_uadv_arr;
 utb sys.dbms_uadv_arr;
 ret NUMBER;
BEGIN
  uar := sys.dbms_uadv_arr(900,1800,3600);
  utb := sys.dbms_uadv_arr();
  ret := dbms_undo_adv.required_undo_size(uar, utb, 250, 270);
  dbms_output.put_line(ret);
  dbms_output.put_line(utb(1));
  dbms_output.put_line(utb(2));
  dbms_output.put_line(utb(3));
END;
/
 
UNDO_ADVISOR
Uses the advisor framework to identify problems and provide recommendations

Overload 1
dbms_undo_adv.undo_advisor(
starttime IN DATE,
endtime   IN DATE,
instance  IN NUMBER)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 v VARCHAR2(300);
BEGIN
  v := dbms_undo_adv.undo_advisor(SYSDATE-1/1440, SYSDATE, 1);
  dbms_output.put_line(v);
END;
/
Finding 1:There is not enough data in AWR.

PL/SQL procedure successfully completed.
Overload 2 dbms_undo_adv.undo_advisor(instance IN NUMBER)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 v VARCHAR2(100);
BEGIN
  v := dbms_undo_adv.undo_advisor(1);
  dbms_output.put_line(v);
END;
/
Finding 1:The undo tablespace is OK.

PL/SQL procedure successfully completed.
Overload 3 dbms_undo_adv.undo_advisor(
s1       IN NUMBER,
s2       IN NUMBER,
instance IN NUMBER)
RETURN VARCHAR2;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set serveroutput on

DECLARE
 retval VARCHAR2(200);
BEGIN
  retval := dbms_undo_adv.undo_advisor(3485, 3509, 1);
  dbms_output.put_line(retval);
END;
/
Finding 1:The undo tablespace is OK.

PL/SQL procedure successfully completed.
 
UNDO_AUTOTUNE
Determines if auto tuning of undo retention is enabled for the current undo tablespace. The output parameter is meaningful only when the return value is TRUE. dbms_undo_adv.undo_autotune(autotune_enabled OUT BOOLEAN)
RETURN BOOLEAN;
set serveroutput on

DECLARE
 bp BOOLEAN;
 br BOOLEAN;
BEGIN
  br := dbms_undo_adv.undo_autotune(bp);

  IF bp THEN
    dbms_output.put_line('Meaningful');
  ELSE
    dbms_output.put_line('Not Meaningful');
  END IF;

  IF br THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
Meaningful
True

PL/SQL procedure successfully completed.
 
UNDO_HEALTH
Find out the problem in undo tablespace and provide recommendation to fix the problem. If no problem found, return value is 0

Overload 1
dbms_undo_adv.undo_health(
problem        OUT VARCHAR2,
recommendation OUT VARCHAR2,
rationale      OUT VARCHAR2,
retention      OUT NUMBER,
utbsize        OUT NUMBER)
RETURN NUMBER;
set serveroutput on

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
BEGIN
  retv := dbms_undo_adv.undo_health(prob, reco, rtnl, retn, utbs);
  dbms_output.put_line('Problem: ' || prob);
  dbms_output.put_line('Recmmnd: ' || reco);
  dbms_output.put_line('Rationl: ' || rtnl);
  dbms_output.put_line('Retentn: ' || TO_CHAR(retn));
  dbms_output.put_line('UTBSize: ' || TO_CHAR(utbs));
END;
/
Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

PL/SQL procedure successfully completed.
Overload 2 dbms_undo_adv.undo_health(
starttime       IN  DATE,
endtime         IN  DATE,
problem         OUT VARCHAR2,
recommendation  OUT VARCHAR2,
rationale       OUT VARCHAR2,
retention       OUT NUMBER,
utbsize         OUT NUMBER)
RETURN NUMBER;
set serveroutput on

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
BEGIN
  retv := dbms_undo_adv.undo_health(SYSDATE-1/24, SYSDATE, prob, reco, rtnl, retn, utbs);
  dbms_output.put_line('Problem: ' || prob);
  dbms_output.put_line('Recmmnd: ' || reco);
  dbms_output.put_line('Rationl: ' || rtnl);
  dbms_output.put_line('Retentn: ' || TO_CHAR(retn));
  dbms_output.put_line('UTBSize: ' || TO_CHAR(utbs));
END;
/
Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

PL/SQL procedure successfully completed.
Overload 3 dbms_undo_adv.undo_health(
s1             IN  NUMBER,
s2             IN  NUMBER,
problem        OUT VARCHAR2,
recommendation OUT VARCHAR2,
rationale      OUT VARCHAR2,
retention      OUT NUMBER,
utbsize        OUT NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
BEGIN
  retv := dbms_undo_adv.undo_health(3485, 3509, prob, reco, rtnl, retn, utbs);
  dbms_output.put_line('Problem: ' || prob);
  dbms_output.put_line('Recmmnd: ' || reco);
  dbms_output.put_line('Rationl: ' || rtnl);
  dbms_output.put_line('Retentn: ' || TO_CHAR(retn));
  dbms_output.put_line('UTBSize: ' || TO_CHAR(utbs));
END;
/
Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

PL/SQL procedure successfully completed.
 
UNDO_INFO
Returns current undo tablespace name, max possible size, if it is auto extensible, current undo retention value and if the undo tablespace has guaranteed undo retention dbms_undo_adv.undo_info(
table_space_name    OUT VARCHAR2,
table_space_size    OUT NUMBER,
auto_extend         OUT BOOLEAN,
undo_retention      OUT NUMBER,
retention_guarantee OUT BOOLEAN)
RETURN BOOLEAN;
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;
/
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

PL/SQL procedure successfully completed.

Related Topics
Built-in Functions
Built-in Packages
Oracle Enterprise Manager
Tablespaces
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx