EXP-00091和IMP-00010解決辦法

在session4的4小題需要用到SH.COUNTRIES表，而手工建庫時，這個SH使用者沒有這個表，就想著將別處的這個表拷貝過來。
原來匯出表的庫：database Oracle_SID=TEST0221 版本： 11.2.0.4需要導進的庫：database ORACLE_SID=PROD 版本：10.2.0.1因為字元集的問題，出現了EXP-00091的錯誤因為資料庫版本的問題，出現了IMP-00010的錯誤 [oracle@node2 ~]$exp sh/sh@test0221 tables=COUNTRIES rows=y file=COUNTRIES.dmp Export: Release 11.2.0.4.0 - Production on Thu Mar 27 15:19:07 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path .... . exporting table COUNTRIES 23 rows exportedEXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.Export terminated successfully with warnings.[oracle@node2 ~]$ oerr exp 0009100091, 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.[oracle@node2 ~]$ exitexit sys@TEST0221> select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET'; PARAMETER VALUE------------------------------ -------------------------------------------------------------------NLS_CHARACTERSET AL32UTF8 或者查看：sys@TEST0221> select * from v$nls_parameters where parameter='NLS_CHARACTERSET'; PARAMETER VALUE---------------------------------------------------------------- ----------------------------------------------------------------NLS_CHARACTERSET AL32UTF8 sys@TEST0221> ![oracle@node2 ~]$export NLS_LANG=AMERICAN_AMERICA.AL32UTF8--設定字元集[oracle@node2 ~]$exp sh/sh@test0221 tables=COUNTRIES rows=y file=COUNTRIES.dmp--再來重新匯出，成功 Export: Release 11.2.0.4.0 - Production on Thu Mar 27 15:22:32 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in AL32UTF8 character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path .... . exporting table COUNTRIES 23 rows exportedExport terminated successfully without warnings. [oracle@node2 ~]$ lltotal 480-rw-r--r-- 1 oracle oinstall 438823 Mar 14 13:30 awrrpt_1_33_37.html-rw-r--r-- 1 oracle oinstall 24576 Mar 27 15:22COUNTRIES.dmp-rw-r--r-- 1 oracle oinstall 2992 Feb 24 23:00 login.sql-rw-r--r-- 1 oracle oinstall 195 Mar 14 08:43 spcpkg.lis-rw-r--r-- 1 oracle oinstall 5477 Mar 14 08:43 spctab.lis-rw-r--r-- 1 oracle oinstall 1832 Mar 14 08:43 spcusr.lis [oracle@node2 ~]$scp COUNTRIES.dmp 192.168.1.155:/home/oracle--- 使用scp將檔案傳輸到需要匯入的庫的系統裡oracle@192.168.1.155's password:COUNTRIES.dmp 100% 24KB 24.0KB/s 00:00[oracle@node2 ~]$ 在需要匯入表的環境裡查看剛scp傳輸過來的檔案： [oracle@ocm1 ~]$ lltotal 32-rw-r--r-- 1 oracle oinstall 24576 Mar 27 15:26 COUNTRIES.dmpdrwxr-xr-x 2 oracle oinstall 4096 Mar 27 12:39 scriptdrwxr-xr-x 2 oracle oinstall 4096 Mar 19 14:51 temp   ##################################################################### 下面是匯入： [oracle@ocm1 ~]$ imp Import: Release 10.2.0.1.0 - Production on Thu Mar 27 15:31:30 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: shPassword: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options Import file: expdat.dmp > /home/oracle/COUNTRIES.dmp Enter insert buffer size (minimum is 8192) 30720> IMP-00010: not a valid export file, header failed verificationIMP-00000: Import terminated unsuccessfully[oracle@ocm1 ~]$  頭部驗證失敗是由於版本號碼不同所致，經實驗可以通過如下方法進行修改：可以看到頭部資訊 －－TEXPORT:V11.01.00，即為來源資料庫的版本號碼，將其修改為目的資料庫的版本號碼，如本機為10.02.01，再次進行匯入操作，匯入成功[oracle@ocm1 ~]$vi COUNTRIES.dmp --直接使用vi修改下頭部的版本號碼即可^C^CiEXPORT:V10.02.00--之前是EXPORT:V11.02.00USH  再次匯入，成功： [oracle@ocm1 ~]$ imp Import: Release 10.2.0.1.0 - Production on Thu Mar 27 15:36:22 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: shPassword: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options Import file: expdat.dmp > /home/oracle/COUNTRIES.dmp Enter insert buffer size (minimum is 8192) 30720> Export file created by EXPORT:V10.02.00 via conventional pathimport done in AL32UTF8 character set and AL16UTF16 NCHAR character setList contents of import file only (yes/no): no > Ignore create error due to object existence (yes/no): no > Import grants (yes/no): yes > Import table data (yes/no): yes > Import entire export file (yes/no): no >Username: sh Enter table(T) or partition(T:P) names. Null list means all tables for userEnter table(T) or partition(T:P) name or . if done: . importing SH's objects into SHIMP-00008: unrecognized statement in the export file: . importing SH's objects into SH. . importing table "COUNTRIES" 23 rows importedImport terminated successfully with warnings.[oracle@ocm1 ~]$  SYS@PROD> conn sh/shConnected.SH@PROD> desc COUNTRIESName Null? Type----------------------------------------- -------- ----------------------------COUNTRY_ID NOT NULL NUMBERCOUNTRY_ISO_CODE NOT NULL CHAR(2)COUNTRY_NAME NOT NULL VARCHAR2(40)COUNTRY_SUBREGION NOT NULL VARCHAR2(30)COUNTRY_SUBREGION_ID NOT NULL NUMBERCOUNTRY_REGION NOT NULL VARCHAR2(20)COUNTRY_REGION_ID NOT NULL NUMBERCOUNTRY_TOTAL NOT NULL VARCHAR2(11)COUNTRY_TOTAL_ID NOT NULL NUMBERCOUNTRY_NAME_HIST VARCHAR2(40) SH@PROD>SH@PROD> select count(*) from COUNTRIES; COUNT(*)----------23