APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

This is a busy database.but no data found in view dba_hist_undostat.

SQL>select * from dba_hist_undostat;

no rows selected

AWR snapshots complete successfully,but the view wrh$_undostat is also empty.

CHANGES

Parameter _undo_autotune was set to false recently

CAUSE

This issue is caused by _undo_autotune=FALSE

Auto undo tuning has been disabled.

If the parameter _undo_autotune=FALSE, there will be no data generated in table X$KTUSMST2 which is the source table of view dba_hist_undostats

SOLUTION

Proper Solution:

Please modify parameter _undo_autotune to true,you can use following commad:

alter system set "_undo_autotune"=true scope=both;