[20130208]物化视图建立以及删除的问题.txt

[20130208]物化视图建立以及删除的问题.txt

SQL> select * from v$version where rownum

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create materialized view v_dept as select * from dept ;

SQL> SELECT object_name, object_id, data_object_id, object_type, status  FROM dba_objects WHERE wner = USER AND object_name = 'V_DEPT';

OBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         STATUS

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

V_DEPT                  269229         269229 TABLE               VALID

V_DEPT                  269231                MATERIALIZED VIEW   VALID

--如果删除物化视图，对应的表也一起删除。

SQL> drop MATERIALIZED view v_dept ;

Materialized view dropped.

SQL> SELECT object_name, object_id, data_object_id, object_type, status  FROM dba_objects WHERE wner = USER AND object_name = 'V_DEPT';

no rows selected

--如果要保证在删除物化视图时，表V_DEPT不删除，一般是建立物化视图时使用如下参数：

SQL> create table v_dept as select * from dept where rownum

SQL> create materialized view v_dept ON PREBUILT TABLE as select * from dept ;

SQL> SELECT object_name, object_id, data_object_id, object_type, status  FROM dba_objects WHERE wner = USER AND object_name = 'V_DEPT';

OBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         STATUS

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

V_DEPT                  269232         269232 TABLE               VALID

V_DEPT                  269234                MATERIALIZED VIEW   VALID

SQL> drop MATERIALIZED view v_dept ;

Materialized view dropped.

SQL> SELECT object_name, object_id, data_object_id, object_type, status  FROM dba_objects WHERE wner = USER AND object_name = 'V_DEPT';

OBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         STATUS

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

V_DEPT                  269232         269232 TABLE               VALID

--今天看了手册，实际上删除物化视图时可以加入preserve table参数，这样可以保留相应的表。

测试如下：

SQL> drop table v_dept purge ;

Table dropped.

SQL> create materialized view v_dept as select * from dept ;

Materialized view created.

SQL> SELECT object_name, object_id, data_object_id, object_type, status  FROM dba_objects WHERE wner = USER AND object_name = 'V_DEPT';

OBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         STATUS

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

V_DEPT                  269235         269235 TABLE               VALID

V_DEPT                  269237                MATERIALIZED VIEW   VALID

SQL> drop MATERIALIZED view v_dept preserve table;

Materialized view dropped.

SQL> SELECT object_name, object_id, data_object_id, object_type, status  FROM dba_objects WHERE wner = USER AND object_name = 'V_DEPT';

OBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         STATUS

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

V_DEPT                  269235         269235 TABLE               VALID

--可以发现表v_dept依旧存在。
--这样也帮别人解决一个物化视图的问题。