From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
To: <smishra_97@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 2 May 2008 08:38:33 -0400
(Sorry about the first email!)
Sanjay,
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 snaptime
from 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.
Tom
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sanjay Mishra
Sent: Thursday, May 01, 2008 9:52 PM
To: oracle-l@xxxxxxxxxxxxx
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
TIA
Sanjay
________________________________
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try
it now.
<http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i6
2sR8HDtDypao8Wcj9tAcJ>
