imp,exp导入导出的一些错误

使用exp导出数据源，提示错误，

$ exp system/oracle file=/home/oracle/airline.dmp tables=airline owner=user_a
…
EXP-00026: conflicting modes specified
…

EXP-00026表示参数冲突，

$ oerr EXP 00026 00026, 00000, "conflicting modes specified" // *Cause:  Conflicting export modes were specified. // *Action: Specify only one parameter and retry

就用了俩参数，所以猜测owner和tables参数有冲突，改为owner.table_name，可以导出，

$ exp system/oracle file=/home/oracle/airline.dmp tables=user_a.airline

...

. . exporting table              AIRLINE       1000 rows exported EXP-00091: Exporting questionable statistics.

EXP-00091错误比较常见，

$ oerr exp 00091 00091, 00000, "Exporting questionable statistics." // *Cause:  Export was able export statistics, but the statistics may not be //          usuable. The statistics are questionable because one or more of //          the following happened during export: a row error occurred, client //          character set or NCHARSET does not match with the server, a query //          clause was specified on export, only certain partitions or //          subpartitions were exported, or a fatal error occurred while //          processing a table. // *Action: To export non-questionable statistics, change the client character //          set or NCHARSET to match the server, export with no query clause, //          export complete tables. If desired, import parameters can be //          supplied so that only non-questionable statistics will be imported, //          and all questionable statistics will be recalculated.

一般就是字符集设置问题，需要操作系统NLS_LANG环境变量值，和数据库字符集一致，如下所示操作系统NLS_LANG值，未被设置，

$ echo $NLS_LANG
为空

数据库字符集是AMERICAN_AMERICA.AL32UTF8，

SQL select userenv(‘language’) from dual;
USERENV(‘LANGUAGE’)

---

AMERICAN_AMERICA.AL32UTF8

设置NLS_LANG值为AMERICAN_AMERICA.AL32UTF8，

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

$ echo $NLS_LANG AMERICAN_AMERICA.AL32UTF8

再次导入，就不会提示“EXP-00091: Exporting questionable statistics.”的错误了，

$ exp system/oracle file=/home/oracle/airline.dmp tables=user_a.airline

...

. . exporting table              AIRLINE       1000 rows exported Export terminated successfully without warnings.

由于和这篇文章《》一样的情况，目标库存在和原始库，相同的表空间名，但是目标用户默认表空间，并不是这个表空间名称，因此若用常规方法导入，不会进入用户默认表空间，需要一些处理，可以参考《》的方法。

但是导入操作，提示错误，

imp system/oracle file=/home/ora11g/airline.dmp fromuser=user_a touser=user_b rows=n

...

. importing USER_A's objects into USER_B

. . importing table            "AIRLINE" IMP-00058: ORACLE error 1950 encountered ORA-01950: no privileges on tablespace 'TABLESPACE_IDX'

是因为原始库，索引存在于TABLESPACE_IDX索引表空间，不是数据表空间，即使我用indexes参数=n，不导入索引数据，仍旧提示错误，我猜是因为表存在主键索引，无法屏蔽导入，

imp system/oracle file=/home/ora11g/airline.dmp fromuser=user_a touser=user_b statistics=none indexes=n ignore=y

...

. importing USER_A's objects into USER_B

. . importing table            "AIRLINE" IMP-00058: ORACLE error 1950 encountered ORA-01950: no privileges on tablespace 'TABLESPACE_IDX'

只能临时赋予user_b对于TABLESPACE_IDX的配额，重建索引，指定新的表空间，再删除配额操作，

SQL alter user user_b quota unlimited on tablespace_idx;
SQL alter index pk_airine rebuild tablespace tablespace_idx;
SQL alter user user_b quota 0 on tablespace_idx;

至此，数据已经导入测试库。

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，