创建物化视图添加on prebuilt table的好处是 ，在创建的物化视图的schema下要有与物化视图同名的表，如果更新物化视图，同名表也会被更新。当删除物化视图，不会删除同名的表，且保留从基表更新过来的数据。如果创建物化视图不用on prebuilt table，则不需要在创建的物化视图的schema下要有与物化视图同名的表。

    在用on prebuilt table创建物化视图时，需要注意的几点：

    1.创建的物化视图的schema下必须要有一个与物化视图同名的表。

    2.与物化视图同名的表结构，必须与创建物化视图select query语句字段名相同，且一一对应。



1.在test1下创建基表：

SQL> conn test1/test1

Connected.

SQL> create table testdb1(col1 varchar2(20),col2 number);


Table created.


SQL> alter table testdb1 add constraint pk_a primary key(col1);


Table altered.


SQL> insert into testdb1 values('1',1);


1 row created.


SQL> insert into testdb1 values('2',2);


1 row created.


SQL> commit;


Commit complete.


2.在test1下创建物化视图日志。

SQL> create materialized view log on testdb1 with primary key;


Materialized view log created.


SQL> select * from tab;


TNAME       TABTYPE CLUSTERID

------------------------------ ------- ----------

MLOG$_TESTDB1       TABLE

RUPD$_TESTDB1       TABLE

TESTDB1       TABLE




3.在SYS用户下授予test2相关的权限：

SQL> conn  / as sysdba

Connected.



SQL> grant select on test1.testdb1 to test2;


Grant succeeded.


SQL> grant select on test1.MLOG$_TESTDB1 to test2;


Grant succeeded.


SQL> grant create materialized view to  test2;


Grant succeeded.



4.在test2下创建物化视图

SQL> conn test2/test2

Connected.



--通过下面错误可以看出on prebuilt table创建物化视图必须要创建一张与物化视图同名的表

SQL> create materialized view testdb2 on prebuilt table as select * from test1.testdb1

        *

ERROR at line 1:

ORA-12059: prebuilt table "TEST2"."TESTDB2" does not exist



SQL> create table testdb2(col1 varchar2(20));


Table created.


--通过下面错误可以看出创建的物化视图query数据项必须要与testdb2相同且一一对应

SQL> create materialized view testdb2 on prebuilt table as select * from test1.testdb1;

create materialized view testdb2 on prebuilt table as select * from test1.testdb1

                                                      *

ERROR at line 1:

ORA-12060: shape of prebuilt table does not match definition query



SQL> create materialized view testdb2 on prebuilt table as select col2 from test1.testdb1;

create materialized view testdb2 on prebuilt table as select col2 from test1.testdb1

                                                             *

ERROR at line 1:

ORA-12060: shape of prebuilt table does not match definition query



SQL> create materialized view testdb2 on prebuilt table as select col1 from test1.testdb1;


Materialized view created.


SQL> select count(*) from testdb2;


  COUNT(*)

----------

0



testdb2表已经做物化视图了：

SQL> delete from testdb2;

delete from testdb2

            *

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view





5.更新物化视图

SQL> exec dbms_mview.refresh('testdb2','c');


PL/SQL procedure successfully completed.


SQL> select count(*) from testdb2;


  COUNT(*)

----------

2





SQL> conn test1/test1

Connected.

SQL>  insert into testdb1 values('3','3');


1 row created.


SQL> commit;


Commit complete.



SQL> conn test2/test2

Connected.


SQL> exec dbms_mview.refresh('testdb2','c');


PL/SQL procedure successfully completed.


SQL> select count(*) from testdb2;


  COUNT(*)

----------

3


6.在test2下删除物化视图：

SQL> drop materialized view testdb2;


Materialized view dropped.


--可见同名的表还存在。

SQL> select count(*) from testdb2;


  COUNT(*)

----------

3