sys fails fast refreshing

From: Gerard Lacroix <kochel_verz_at_yahoo.com>
Date: 6 Nov 2005 09:13:06 -0800
Message-ID: <1131297186.806707.322970@g49g2000cwa.googlegroups.com>

Hi everyone.
The following test was made on a Windows XP Professional 2002 SP 2 box. I don't understand why sys user does not perform a fast refresh on a materialized view.
With others users it works fine. May be I am missing something. Thanks in advance.

This is my simple test case:

C:\>sqlplus "sys/sys as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Nov 6 14:44:51 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create table my_objects as select * from user_objects;

Table created.

SQL> alter table my_objects add primary key(object_id);

Table altered.

SQL> create materialized view log on my_objects with primary key including new values;

Materialized view log created.

SQL> create materialized view mv_my_objects   2 refresh fast on demand
  3 as select * from my_objects;

Materialized view created.

SQL> select count(*) from my_objects;

  COUNT(*)

---

     13535

SQL> select count(*) from mv_my_objects;

  COUNT(*)

---

     13535

SQL> delete from my_objects where rownum <=100;

100 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from mv_my_objects;

  COUNT(*)

---

     13535

SQL> select count(*) from my_objects;

  COUNT(*)

---

     13435

SQL> exec dbms_mview.refresh('mv_my_objects', 'F');

PL/SQL procedure successfully completed.

SQL> select count(*) from mv_my_objects;

  COUNT(*)

---

     13535

---

C:\>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Nov 6 14:50:31 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create table my_objects as select * from user_objects;

Table created.

SQL> alter table my_objects add primary key(object_id);

Table altered.

SQL> create materialized view log on my_objects with primary key including new values;

Materialized view log created.

SQL> create materialized view mv_my_objects   2 refresh fast on demand
  3 as select * from my_objects;

Materialized view created.

SQL> select count(*) from my_objects;

  COUNT(*)

---

         8

SQL> select count(*) from mv_my_objects;

  COUNT(*)

---

         8

SQL> delete from my_objects where rownum <=4;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from mv_my_objects;

  COUNT(*)

---

         8

SQL> select count(*) from my_objects;

  COUNT(*)

---

         4

SQL> exec dbms_mview.refresh('mv_my_objects', 'F');

PL/SQL procedure successfully completed.

SQL> select count(*) from mv_my_objects;

  COUNT(*)

---

         4

SQL> Received on Sun Nov 06 2005 - 11:13:06 CST