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的個人雜貨鋪，

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！