Re: Materialized View Needing to be Refreshed
- From: "Thomas Day" <tomdaytwo@xxxxxxxxx>
- To: Thomas.Mercadante@xxxxxxxxxxxxxxxxx
- Date: Fri, 19 Jan 2007 11:47:29 -0500
Here's a cript that I used to use to monitor my snapshot refreshes.
On 1/19/07, Mercadante, Thomas F (LABOR) <
Thomas.Mercadante@xxxxxxxxxxxxxxxxx> wrote:
All,
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?
Thanks
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.
--qmlogs.sql
--RUN AT MASTER SITE
COL LOG_OWNER FORMAT A8
COL OWNER FORMAT A10
COL MASTER FORMAT A25
COL NAME FORMAT A32
COL LOG_TABLE FORMAT A30
COL SNAPSHOT_SITE FORMAT A24
SELECT UNIQUE
*
FROM
((SELECT
OWNER
,NAME
,SNAPSHOT_SITE
,CAN_USE_LOG
,REFRESH_METHOD
, LOG_OWNER
,LOG_TABLE
,ROWIDS
,PRIMARY_KEY
,CURRENT_SNAPSHOTS
,A.SNAPSHOT_ID SID
FROM user_snapshot_logs A
, user_REGISTERED_SNAPSHOTS B
WHERE B.SNAPSHOT_ID = A.SNAPSHOT_ID(+)
)
UNION
(SELECT
OWNER
,NAME
,SNAPSHOT_SITE
,CAN_USE_LOG
,REFRESH_METHOD
, LOG_OWNER
,LOG_TABLE
,ROWIDS
,PRIMARY_KEY
,CURRENT_SNAPSHOTS
,A.SNAPSHOT_ID SID
FROM user_snapshot_logs A
, user_REGISTERED_SNAPSHOTS B
WHERE A.SNAPSHOT_ID = B.SNAPSHOT_ID(+))
ORDER BY 1,2,3)
/
Other related posts:
