RE: Materialized View

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 2 May 2008 08:38:33 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF01C49F50@EXCNYSM0A1AJ.nysemail.nyenet>

(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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra Sent: Thursday, May 01, 2008 9:52 PM
To: oracle-l_at_freelists.org
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>

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

Received on Fri May 02 2008 - 07:38:33 CDT