Oracle Materialized View - How to find last Refresh Stats on View

Posted on 2009-06-30
Last Modified: 2012-05-07
Is there a sys view I can query to find out the last run date stats on a particular materialized view?

Question by:iBinc
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 40

Expert Comment

ID: 24749084
LVL 40

Expert Comment

ID: 24749103
select last_refresh_type, last_refresh_date, staleness from dba_mviews where mview_name = 'MYVIEW';

You may also use USER_MVIEWS if not a DBA


Author Comment

ID: 24749305
Tried using the user_mviews but the table is empty. I think this user is read only to the source. Any way around that?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 24749345
user_mviews doesn't return anything even where I have dba access. ??
LVL 40

Accepted Solution

mrjoltcola earned 500 total points
ID: 24749450

If you don't it there, you really should check DBA_MVIEWS to be sure it even exists.

DBA_MVIEWS will show all in the system
USER_MVIEWS only shows what you have privileges for.

Who owns the mview?

Author Comment

ID: 24749699
all_mviews works for me!

Thanks for all your contributions!
LVL 40

Expert Comment

ID: 24749724
You are very welcome!

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
capture vmstat info and insert it into an oracle table 31 93
return value based on substr 10 73
Oracle programming for starter 14 75
Read CLOB data from Oracle using JAVA 3 40
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question