Oracle DBMS_UNDO_ADV
Version 19.3

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 with a PUBLIC synonym
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;
Overload 2 dbms_undo_adv.best_possible_retention RETURN NUMBER;
SELECT dbms_undo_adv.best_possible_retention
FROM dual;
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;

SELECT dbms_undo_adv.best_possible_retention(578, 600)
FROM dual;
 
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-1/24, SYSDATE)
FROM dual;
Overload 2 dbms_undo_adv.longest_query RETURN NUMBER;
SELECT dbms_undo_adv.longest_query
FROM dual;
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(578, 600)
FROM dual;
 
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;
Overload 2 dbms_undo_adv.rbu_migration RETURN NUMBER;
SELECT dbms_undo_adv.rbu_migration
FROM dual;
 
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;

SELECT dbms_undo_adv.required_retention(SYSDATE-12/1440, SYSDATE)
FROM dual;
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(578, 600)
FROM dual;
 
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;
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;

SELECT dbms_undo_adv.required_undo_size(1440)
FROM dual;
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, 578, 600)
FROM dual;
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;
/
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;
/
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;
/
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;
/
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(578, 600, 1);
  dbms_output.put_line(retval);
END;
/
 
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;
/
 
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;
/
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;
/
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(578, 600, 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;
/
 
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;
/

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

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