Solved

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

Posted on 2009-06-30
7
1,496 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
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!

 

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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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