Oracle物化视图之onprebuilttable
创建物化视图添加on prebuilt table的好处是 ，在创建的物化视图的schema下要有与物化视图同名的表，如果更新物化视图，同名表也会被更新。当删除物化视图，不会删除同名的表，且保留从基表更新过来的数据。如果创建物化视图不用on prebuilt table，则不需要在创建的物化视图的schema下要有与物化视图同名的表。
创新互联长期为上1000+客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为奉节企业提供专业的成都网站建设、网站建设，奉节网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。
在用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 TABTYPECLUSTERID
------------------------------ ------- ----------
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
分享题目：Oracle物化视图之onprebuilttable
URL分享：http://mswzjz.cn/article/jhoehp.html