Re: sys fails fast refreshing

From: Gerard Lacroix <kochel_verz_at_yahoo.com>
Date: 6 Nov 2005 13:06:19 -0800
Message-ID: <1131311179.570923.134090@f14g2000cwb.googlegroups.com>

Maxim Demenko ha escrito:

> 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

Thanks Maxim.
I should have noticed it was clearly documented. In fact, I did read that manual. Obviously, I skipped that paragraph.

Anyway, I wonder why Oracle lets you create something you will not be able to use later. And when you use it, displays a "PL/SQL procedure successfully completed" message.

Regards. Received on Sun Nov 06 2005 - 15:06:19 CST