RE: Materialized View Needing to be Refreshed

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 19 Jan 2007 14:59:24 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08A60@EXCNYSM0A1AJ.nysemail.nyenet>

All,  

All of these replies were perfect! I got what I wanted from you all and fashioned a report to run to help us understand what MV's need refreshing.

Thanks again

Tom  

---

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

---

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Friday, January 19, 2007 1:06 PM
To: Mercadante, Thomas F (LABOR)
Subject: Re: Materialized View Needing to be Refreshed    

On 1/19/07, Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us> wrote:

I have a table with MV logs in a source database. Two other databases have created MV's based on this table. Is there some way to determine which database needs to refresh their MV so that all of the MV logs will be cleared?

Hi Tom,

The keys to determining which snapshots need refreshed are found in the DBA_SNAPSHOT_LOGS view on the destination site, and the sys.snap$ table on the source sites.

The DBA_SNAPSHOT_LOGS view contains the SNAPSHOT_ID column, which you won't find anywhere else.

On the destination site:

select

   log_owner
,master
,log_table
,snapshot_id
,to_char(current_snapshots,'mm/dd/yyyy hh24:mi:ss') current_snapshots

from dba_snapshot_logs
order by 1

On the source sites, this will get the snap_id the MV's:

select sowner,vname,tname, snapid
from sys.snap$
/

This is easiest to test by creating a table and 2 MV's on a single database, and running these queries.

Attached are some notes I made last year while playing with MV's.

HTH,  

-- 

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 19 2007 - 13:59:24 CST