Oracle DBMS_UNDO_ADV
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Undo advisor gives users recommendation on setting undo retention and sizing the undo tablespace.
AUTHID DEFINER
Data Types CREATE OR REPLACE TYPE sys.dbms_uadv_arr AS VARRAY(100) OF NUMBER;
/
Dependencies
CDB_TABLESPACES DBMS_UNDOADV_LIB V$DATABASE
DBA_TABLESPACES PLITBLM V$PARAMETER
DBMS_SQL PRVTEMX_ADMIN V$ROLLSTAT
DBMS_SYS_ERROR PRVT_ADVISOR X$KSPPCV2
DBMS_UADV_ARR UTL_LMS X$KSPPI
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
OEM Grid Control
Packages
Tablespaces

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-2014 Daniel A. Morgan All Rights Reserved