Re: Materialized View

On 5/2/08, Sanjay Mishra <smishra_97_at_yahoo.com> 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.

Thanks.

-- 
Regards,
Peter Teoh
--
http://www.freelists.org/webpage/oracle-l