RE: Materialized View
Date: Fri, 2 May 2008 08:38:33 -0400
(Sorry about the first email!)
Are you asking who and where does an MV exist pointing back to a table in your database? If so, then the sql below can tell you this:
select reg.owner owner,reg.name snapname,reg.snapshot_site snapsite,reg.snapshot_id snapid,
sl.log_owner,nvl(sl.master,'Missing MV Log') master_table, sl.log_table,sl.current_snapshots snaptimefrom dba_snapshot_logs sl,dba_registered_snapshots reg where sl.snapshot_id (+) = reg.snapshot_id order by 1,2;
"Owner" is the owner of the MV
"SnapSite" is where the MV exists
"SnapName" is the name of the MV
"Master Table" is the table that the MV is querying from
So run this query on the machine where the MV logs exist. It will show you where the MV's querying your table are coming from.
Hope this helps.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra Sent: Thursday, May 01, 2008 9:52 PM
Subject: Materialized View
How can we find that who is using this materialized view. I had a database where MV log is created and is very big but cannot purge as I am not aware who has created. How we can say that this Log is used by which server or site
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try