Oracle Database Discussions

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.
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>