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

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180205B03XZA00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券