Re: Materialized View

From: Peter Teoh <>
Date: Fri, 2 May 2008 11:46:01 +0800
Message-ID: <>

On 5/2/08, Sanjay Mishra <> wrote:
> 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

Two path possible:

  1. Currently online users - whether they are using it or not. This is easy, just query the V$SQLAREA for for legacy SQL.
  2. Offline users - whether any existing stored procedures or tables or views are dependent on it or not. THis is difficult, as the access can be embedded inside stored procedures. After all these, there are still those anonymous stored procedures, which users stored inside their own sql scripts - it is not possible to anticipate any of these scripts will access the object or not.

Generally, i think the problem is a difficult one.


Peter Teoh
Received on Thu May 01 2008 - 22:46:01 CDT

Original text of this message