Why are we selecting from sys.mlog$, while refreshing materialized view
rahulrasJun 16 2009 — edited Jun 16 2009Hi All,
I have a materialized view sitting on Oracle 10.2 (lets name the mview 'M1').
This M1 select data from a view (NOT materialized view) on remote database, which is in 9.2. Lets name the view on 9.2 as V1.
When I refresh data in M1 on 10g, I do,
DBMS_SNAPSHOT.REFRESH( materialized_view_name , 'C', atomic_refresh => FALSE);
Now, when I run the query of view V1 on the 9i database (just for testing) , it takes hardly a second and returns data.
When I am refreshing mview M1, with the same query, it is taking for ever.
I checked on 9i db (when the query is executing on 10g), it shows the session from remote database in v$session and on the 9i db i.e. from where I am selecting data (my remote db), it is running query
select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn from sys.mlog$ where mowner = :1 and master = :2
Why is that ???
When I am refreshing mview from a simple view, why is Oracle running queries on sys.mlog$ on remote database ? I am expecting the simple SELECT command to run and I should get data.
Can anybody help ??
Thanks in advance.