物化视图导出导入可能导致物化视图日志的失效

将一个模式的数据导出后，完全导入到另一个模式中，这个过程可能会出错，这是由于对于一些对象会参考其他的对象，而且通过schema.object_name的方式明确指出了所参考的对象，这种情况就有可能造成问题。但是现在碰到的这个问题更加奇怪，应该是Oracle的bug。




测试如下：


SQL> conn / as sysdba
Connected.
SQL> create user a identified by a;

User created.

SQL> grant resource, connect to a;

Grant succeeded.

SQL> grant create materialized view to a;

Grant succeeded.

SQL> create user b identified by b;

User created.

SQL> grant create materialized view to b;

Grant succeeded.

SQL> grant connect , resource to b;

Grant succeeded.

SQL> conn a/a
Connected.
SQL> select * from tab;

no rows selected

SQL> create table t (id number primary key);

Table created.

SQL> create materialized view log on t;

Materialized view log created.

SQL> create materialized view mv_t as select * from t;

Materialized view created.

SQL> insert into t values (1);

1 row created.

SQL> col change_vector$$ format a40
SQL> select * from mlog$_t;

    ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ---------- - - ----------------------------------------
     1 01-1月 -00 I N FE

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('mv_t');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from mv_t;

    ID
----------
     1

SQL> host exp a/a file=test.dmp   

Export: Release 9.2.0.4.0 - Production on 星期三 4月 6 18:03:58 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user A
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user A
About to export A's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export A's tables via Conventional Path ...
. . exporting table            MLOG$_T     0 rows exported
. . exporting table              MV_T     1 rows exported
. . exporting table            RUPD$_T
. . exporting table               T     1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

SQL> host imp b/b file=test.dmp full=y

Import: Release 9.2.0.4.0 - Production on 星期三 4月 6 18:04:23 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by A, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing A's objects into B
. . importing table           "MLOG$_T"     0 rows imported
. . importing table             "MV_T"     1 rows imported
. . importing table              "T"     1 rows imported
IMP-00015: following statement failed because the object already exists:
"CREATE SNAPSHOT LOG ON "A"."T" WITH PRIMARY KEY EXCLUDING NEW VALUES USING "
"("MLOG$_T", (5, 'REPDB01.US.ORACLE.COM', 98, '2005-04-06:17:24:47', '2005-0"
"4-06:17:24:47', '2005-04-06:17:23:04', '4000-01-01:00:00:00', '4000-01-01:0"
"0:00:00', 1, "ID", '2005-04-06:17:23:04', 2, 1, 118, '2005-04-06:17:24:47',"
" ("RUPD$_T")))"
Import terminated successfully with warnings.

SQL> insert into t values (2);

1 row created.

SQL> select * from mlog$_t;

no rows selected

SQL> exec dbms_mview.refresh('mv_t')

PL/SQL procedure successfully completed.

SQL> select * from mv_t;

    ID
----------
     1

SQL> select * from t;

    ID
----------
     1
     2

建立了一个用户a，在这个用户下建立了一个表，并在这个表上面建立了物化视图。然后导出方案a，全部导入到方案b中。导入的时候出错。建立物化视图日志的操作没有发生在b方案中，而是又发生在a方案中，且因为对象已经存在而报错。正如开头所说的，这是可以理解的，奇怪的事情在后面。

发现物化视图日志失效了，随后的DML操作没有被记录到物化视图日志中，这也必然导致了随后的刷新操作得不到正确的结果。

我在9204 for solaris和9201 for windows下分别进行了测试，结果是一样的。看来和物化视图有关的bug还真是不少。