Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-06-30
7
Medium Priority
?
1,607 Views
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?

thanks
0
Comment
Question by:iBinc
  • 4
  • 3
7 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24749084
DBA_MVIEWS.LAST_REFRESH_DATE
0
 
LVL 40

Expert Comment

by:mrjoltcola
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

0
 

Author Comment

by:iBinc
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?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

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

Accepted Solution

by:
mrjoltcola earned 2000 total points
ID: 24749450
Try ALL_MVIEWS

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?
0
 

Author Comment

by:iBinc
ID: 24749699
all_mviews works for me!

Thanks for all your contributions!
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24749724
You are very welcome!
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

886 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