Snapshots- Last Refresh date - SQL & PL/SQL

I am creating a lot of Snapshots with Views for my Project.
The Last_Refresh column in User_Snapshots always shows the value
as 01-JAN-1950 12:00:00. It is not showing the exact Snapshot
Refresh Date. Have anyone of u know why this is so. Is it a Bug?


Materialized View Refresh Time HISTORY

Can we find the History of Refresh Time for Materialized View ....
<i.e> Can we find that in last 4-5 days at what-what time a particular MV has refreshed.. ??
Plz help 
SELECT * FROM ALL_MVIEW_REFRESH_TIMES where last_refresh between '29-OCT-10' AND '31-OCT-10';or
SELECT * FROM ALL_MVIEW_REFRESH_TIMES where last_refresh > '28-OCT-10';Thanks
Edited by: CKPT on Nov 1, 2010 12:23 PM 
Hope this will help! 
I didn't fully understood your question, but you may be looking something like this. 
No but this won't help ... because what i want is the refresh Time of my MV's in last 4-5 days ...
And here i have 4 MV's ...which gets refreshed in every 2 hrs the 'last_refresh' Column only contains the last refreshed time....
So any other suggestion ..?? 
Any Suggestions ..?? 
if you are using fast refresh with materialized view logs then selecting from MVIEW_LOG may help.

Snapshot problem!!

I have a snapshot table between 2 ORACLE servers, normally it works fine, but
today when I update at the first one, It takes long time to update at the seconds server.
I don't know how to check what happend to my snapshot.
I'm a junior dba, so
Would you please tell me how to solve or check what happend on my DB?
Thank you,
Do you use dblinke to do the same? If so, check out your network.
Also, does your databases has upto date statistics?
If this happend next time, try to take the statspack and also collect current wait events using v$session_wait.
hi ..Jaffar
>>Also, does your databases has upto date statistics?
can u explain please?
collecting database/schema/table statistics daily/frequently.
Go to the schema where the tables resides and do the following:
SELECT table_name,num_rows,last_analyzed FROM user_tables;
First of all, see whether you can see num_rows with some value, not blank. If you see num_rows contains some value including 0, that means, you have statsitics. In this case, look at the last_analyzed column and see when was the last_analyzed date.
If you felt that the last_analyzed column contains very old date, or table doesn't have num_rows from the abvoe query. use the following command to collect the statistics.
exec dbms_stats.gather_schema_stats('SCHEMANAME',CASCADE=>true,estimate_percent=>10);
I would recommend to collect the stats frequently and upon the behaviour of your application. i.e. who frequently data changes in the tables.
thanks very much..

Help on writing this query.

Hi Folks,
I'm trying to write a query to retrive the following information regarding snapshots. I tried lot of ways and couldn't get the solutions so seeking your help...
Can you send me a query that will give me all this information.
db name of snapshot site
snapshot name and their base table
refresh type (if it is fast, please list he snapshot log used)
refresh group which snapshot belongs to
dbms_job number which perform the refresh, job current status
refresh frequency
last refresh time
If you tried a lot of ways ... post your work and show us what you have done.
What you are being asked to do is query data dictionary views. Do you know which ones?
SELECT view_name
FROM all_views
WHERE view_name LIKE '%<you fill this in>%';That is all the hints you get until you show your work. 
And if you're still stuck, consult the [url]Oracle® Database Reference guide - it explains the data dictionary views referred to by Daniel in detail.
In fact, RTFM should always be you first course of action (that or googling).

DML date in data dictionary

In which data dictionary table I can find the last date of DML that hapened on a certain table ? 
You can use following view and check last_active_time
There should be another view not a performance view, in my case the table was updates 2 years ago; I need to see the view which have the date of 2 years ago 
You probably need to switch on the auditing for this. 
Unless you have auditing enabled and you are saving all the information you won't be able to get to that. There are some DBA_HIST_* views you can cross check but you won't be able to find 2 year old information, once again unless you are saving it.
What is your Oracle version? If you are on 10g, have you checked out ORA_ROWSCN and SCN_TO_TIMESTAMP for this purpose?
It is not guaranteed that you will have correct results in this case.

Report view Issue

Hi All,
I have a report which is taking 5min to run if I check this using compount layout view But If I run the same report using Pivot view its taking ore tahn 15min time to run sometimes its showing runnut error.Why this is happening.. plz help.
Are you finding any difference in sql when you tested in pivot?
set aggregation rule to sum for measures(if applicable), then test.. 
In both the cases, any report taking more than 5-10 sec is worst. Are you having indexes created.