将一个模式的数据导出后，完全导入到另一个模式中，这个过程可能会出错，这是由于对于一些对象会参考其他的对象，而且通过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还真是不少。

看来fromuser touser 没有起作用。

exp/imp的功能还是弱；比如exp有query,imp竟然没有 ：（

不知道10g的数据泵是否有改进

是这样的：
1、drop snapshot log on
2、执行导入操作: imp ...
3、重建物化视图，这样就会包括新的记录.
create materialized view on

呵呵，我导入到其他用户中，还要将原用户的物化视图日志drop掉（在正式环境中不可能drop的），是不是太麻烦了。
就是这个倒霉的bug，让我的物化视图复制停了。

这个问题在10g里也存在,我把运营库的数据倒入到测试里来,发现了相同的问题,后来删除了物化视图的log才好了!

Utilities的import章节有如下解释：

Importing a Snapshot into a Different Schema
Snapshots, snapshot logs, and related items are exported with the schema name explicitly given in the DDL statements; therefore, snapshots and their related items cannot be imported into a different schema.

If you attempt to use FROMUSER and TOUSER to import snapshot data, an error will be written to the Import log file and the items will not be imported.

以前不知道有这个说法，直到在一次数据恢复时碰到这个问题，导致了一场事故才记住了这个

I met the similar situation as well
but out of 5 mview, only 1 is changed to a table, the other 4 looks ok after imp to same user

oracle version: 9.2.0.8.0

不是导入到同一个用户下，而是导入其他用户，但是影响这个导出的用户

If using different way to exp/imp then will not have this problem even import into different schema.

exp system file=test.dmp owner=a
imp system file=test.dmp fromuser=a touser=b

I tested it. The mlog$_t did not got broken.

10G下对A的物化视图没有影响

to helen:
将imp的方式改为fromuser和touser在9204下仍然会导致问题。
如果你的测试没有问题，那么多半使用FULL=Y的方式也不会有问题，在你的版本中，多半Oracle已经修正了这个bug