Oracle物化视图之on prebuilt table

    创建物化视图添加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