Re: sys fails fast refreshing

Gerard Lacroix schrieb:
> 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>
>

Well, it is clearly documented in SQL Reference: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_64a.htm#2064651

<quote>
Specify the schema containing the materialized view log's master table. If you omit schema, Oracle assumes the master table is contained in your own schema. Oracle creates the materialized view log in the schema of its master table. You cannot create a materialized view log for a table in the schema of the user SYS.
</quote>

And materialized view log is obviously needed for fast refresh. However according the documentation i would expect an error by attempt to create materialized view log as user sys ( at least on the 9.2.0.6 ), but i could as well reproduce your test.
It seems also *you cannot create* should be interpreted as *you should not create ... because it will not work anyway...*

Best regards

Maxim Received on Sun Nov 06 2005 - 11:51:10 CST