Skip to Main Content
  • Questions

  • Materialized View Refresh Statistics

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kirk.

Asked: July 20, 2022 - 3:05 pm UTC

Last updated: July 21, 2022 - 10:39 am UTC

Version: 19c

Viewed 1000+ times

You Asked

We are having an issue with Materialized View Refreshes and I wanted to look up the stats on some of the latest refreshes. I run the query.

SELECT mv_owner, mv_name, refresh_method
FROM dba_mvref_stats
WHERE mv_owner = my_schema


When I do the only thing I see are the MVs with the names like this.

BIN$5D0A7WYY6WvgU6ZTEKyK+w==$0

If I am right that is an object in the recycle bin. Am I correct on that? If so why is this happening?

and Chris said...

Yes, those are recyclebin object names.

I don't know what's going on here. As the docs say:

When you drop a materialized view, Oracle Database does not place it in the recycle bin

Can you reproduce this? Does purging the recyclebin remove these entries?

I can't reproduce this, so if it's causing you issues contact support.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kirk, July 20, 2022 - 5:36 pm UTC

Chris, Thanks for your input. It seems that this is an Out of Place MV refresh and when this happens it only shows the refresh stats for the item that is in the recycle bin. If you take off the out of place refresh it will show up in the stats tables just fine. I did check the recyclebin and those objects are there. If I purge that recyclebin they go away and when I query dba_mvref_stats they are gone too. So it seems that one can not check refresh stats on an out of place MV refresh and once you purge the recycle bin you purge the refresh stats too.
Chris Saxon
July 21, 2022 - 10:39 am UTC

Thanks for letting us know!

Yes, it seems if you purge the recyclebin you'll lose the history. Details of the last refresh time are always available in the *_mviews views:

select mview_name, ( last_refresh_end_time - last_refresh_date ) * 86400 
from   user_mviews;


If this history is important to you might want to capture the stats and save them to your own logging table.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.