学步园

创建物化视图
SQL> create materialized view emp as select * from scott.emp;

Materialized view created.

SQL> select object_name,object_type from user_objects where object_name='EMP';

OBJECT_NAME OBJECT_TYPE
------------------
EMP TABLE
EMP UNDEFINED

删除物化视图
SQL> drop materialized view emp;
Materialized view dropped.
以上2个对象都被删除了，包括UNDEFINED的EMP
SQL> select object_name,object_type from user_objects where object_name='EMP';
No row selected。
先手工创建表
SQL> create table emp as select * from scott.emp;
Table created.
使用on prebuilt table注册新的物化视图，注意view名称必须和表名称一样。
SQL> create materialized view emp on prebuilt table as select * from scott.emp;

Materialized view created.

SQL> select object_name,object_type from user_objects where object_name='EMP';

OBJECT_NAME OBJECT_TYPE
------------------
EMP TABLE
EMP UNDEFINED

表emp已经作为物化视图了。
SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

删除物化视图后，原来的表未被删除。 使用on prebuilt table创建的物化视图被删除后，原来的表不被删除。
SQL> drop materialized view emp;
Materialized view dropped.

SQL> select object_name,object_type from user_objects where object_name='EMP';

OBJECT_NAME OBJECT_TYPE
------------------
EMP TABLE