Forum Stats

  • 3,851,945 Users
  • 2,264,053 Discussions
  • 7,904,914 Comments

Discussions

DBA_HIST_UNDOSTAT returns no rows within PDBs

Peter_L_
Peter_L_ Member Posts: 135 Bronze Badge
edited Oct 10, 2018 10:00AM in Multitenant

Hi,

currently I'm analyzing an issue with autotuned undo retention (Very high "TUNED_UNDORETENTION" => It's equal to instance runtime ) .

While doing this I've noticed that DBA_HIST_UNDOSTAT only returns rows if you query it in the CDB$ROOT. In all PDBs it will returns no rows.

We have local undo in PDBs enabled and Oracle 12.2.0.1 running on OL 7.5. Diagnostic Pack is licensed and enabled.

The alternative view  CDB_HIST_UNDOSTAT returns only rows for container id 1, which is cdb$root.

Does anybody have the same behavior and an explanation/solution?

Cheers Peter

Answers

  • Praveen Ch-Oracle
    Praveen Ch-Oracle Member Posts: 12 Employee
    edited Oct 10, 2018 5:32AM

    Hi Peter,

    Connect to any PDB and run below query.Check if you are getting result.

    select NAME,SNAP_ID,UNDOTSN,UNDOBLKS,TXNCOUNT,MAXQUERYLEN,MAXQUERYSQLID

    from V$CONTAINERS c , DBA_HIST_UNDOSTAT u

    where c.CON_ID=u.CON_ID

    and u.CON_DBID=c.DBID

    order by NAME;

    Thanks

    Praveen

  • Peter_L_
    Peter_L_ Member Posts: 135 Bronze Badge
    edited Oct 10, 2018 10:00AM

    Hi Praveen,

    thank you for responding.

    Running your query in CDB$ROOT it returns rows, running within PDBs it returns no rows.

    As I mentionedd DBA_HIST_UNDOSTAT is empty in PDBs, hence there can not be joined rows with that view.

    Cheers Peter