UNDO INFORMATION:dbms_undo_adv

Table 1
Function calls within DBMS_UNDO_ADV

Function

Description

Outputs

undo_info

Provides basic information about your undo

Tablespace name

Maximum size possible

Current retention value

If undo is auto extensible

If undo is guaranteed undo retention

longest_query

Allows you to see what the longest running query was so
you might be able to tune in relation to time

Length of the longest query

required_retention

Query this function to assist in determining what you
should set undo_retention to in order to help prevent snap-shot-too-old
errors. This value is based on the longest running query.

init.ora parameter undo_retention

best_possible_retention

Provides you with a value for undo_retention that best
fits your current undo tablespace size and usage.

init.ora parameter undo_retention

required_undo_size

Provides a value for the size of the undo tablespace to
create in order to support the current value of the init.ora parameter
undo_retention.

Undo tablespace size

undo_health

Gives descriptive output if any problems are encountered
with your current undo tablespace size or setting of the init.ora parameter
undo_retention and provides recommendations to fix.

Problem / Resolution descriptions

undo_advisor

Uses the advisor framework to give descriptive output if
any problems are encountered with your current undo configuration and
possible resolutions.

Problem / Resolution descriptions

undo_autotune

Tells you if undo auto tuning for undo retention is
enabled

TRUE / FALSE

rbu_migration

Provides the size required for undo tablespace size if you
wish to switch to automatic undo management

Size of Undo Tablespace

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: UNDOTBS
Undo Tablespace Size Is: 28076
Undo Autoextend Is Set To: FALSE
Undo Retention Is: 43200
Undo Guarantee Is Set To: FALSE

PL/SQL procedure successfully completed.

SQL> SELECT dbms_undo_adv.longest_query(SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-1,SYSDATE)
———————————————-
45689
SQL> SELECT dbms_undo_adv.required_retention(SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-1,SYSDATE)
—————————————————
45689
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 43200
undo_tablespace string UNDOTBS

SQL> SELECT dbms_undo_adv.required_undo_size(43200, SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(43200,SYSDATE-1,SYSDATE)
———————————————————
6426

SQL> set serveroutput on
DECLARE
v VARCHAR2(300);
BEGIN
v := dbms_undo_adv.undo_advisor(SYSDATE-1, SYSDATE, 1);
dbms_output.put_line(v);
END;
/
Finding 1:The undo tablespace is OK.

PL/SQL procedure successfully completed.

SQL> 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

undo size based on retention:
SQL> SELECT dbms_undo_adv.required_undo_size(43200)FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(43200)
—————————————
8403