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的个人杂货铺，