EXP客戶端版本差異造成的錯誤

lnwxzyp發表於2011-11-27

客戶端

今天將正準備上線的專案資料庫的使用者下的全部資料導來，一來進行備份；二者也匯入到自己筆記本的資料庫當中進行測試。結果發現用11.2.0.1.0的客戶端來遠端匯出10.2.0.5.0版本的資料就要報錯：

C:\Users\lnwxzyp>exp bz_qudao/qudao_bz@commkh buffer=56000 file=D:\Work\sc_bat\bdump\bz.dmp

EXP-00008: ORACLE error 1003 encountered

ORA-01003: no statement parsed

於是換成10.2.0.4.0的客戶端匯入，結果這次匯出到時能正常匯出成功，但是卻出現一些小錯誤

EXP-00091: Exporting questionable statistics.

查了oracle database 10g error messenges的文件，基本可以確認是字符集的錯誤：

EXP-00091: 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

一開始，沒有理會，直接匯入到本機的資料庫中，經過測試發現不管是10g的客戶端還是11g的客戶端都可以正常匯入，但是匯入之後發現stored procedure裡面的中文註釋全部都是??????，估計就是剛才忽略的錯誤造成的。於是準備把資料清空之後重新匯出、匯入一次。

首先是把匯入的資料清空，寫了一個PL/SQL

declare n varchar2(30);

t varchar2(30);

cursor v is select object_name,object_type from user_objects where object_type<>'INDEX';

begin

open v;

loop

fetch v into n,t;

if t='TABLE' then

execute immediate 'drop '||t||' '||n||' purge ';

else

execute immediate 'drop '||t||' '||n||' ';

end if;

end loop;

close v;

end;

結果就提示表有主外來鍵約束，無法刪除，用下面的語句找出foreign key禁用從鍵

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type = 'R';

執行後再次執行前面的語句，所有的物件就都清空了。

接下來就是要解決字符集的問題：

首先檢視匯出庫的字符集

select * from database_properties where property_name like '%CHARACTERS%';

NLS_CHARACTERSET ZHS16GBK

NLS_NCHAR_CHARACTERSET AL16UTF16

然後同樣的檢視本機的字符集，發現也是一樣的，看來是10g客戶端的字符集不一致造成的亂碼。

開啟登錄檔編輯器，找到HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraClient10g_home1下的NLS_LANG鍵值，發現是為空的。

再看之前匯出時的錯誤訊息：

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

可能預設是US7ASCII的字符集，因此造成了????的亂碼將NLS_LANG的鍵值改為AMERICAN_AMERICA.ZHS16GBK 然後重新開啟一個命令提示符

C:\Users\lnwxzyp>set PATH=D:\oracle\product\10.2.0\client_1\BIN;

--因為我本機上有11g的server還有10g的client，因此先設定PATH為10g的路徑，這個只對當前命令提示符有效因此不必擔心造成什麼麻煩。

然後再次執行匯出

C:\Users\lnwxzyp>exp bz_qudao/123456@srdb buffer=56000 file=D:\Work\sc_bat\bdump\bz.dmp

Export: Release 10.2.0.4.0 - Production on Sun Nov 27 17:50:46 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user BZ_QUDAO

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user BZ_QUDAO

About to export BZ_QUDAO's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export BZ_QUDAO's tables via Conventional Path ...

. . exporting table COMMON_CUSTOMER 0 rows exported

. . exporting table CS_AREA 525 rows exported

. . exporting table CS_AREA_BAK 525 rows exported

. . exporting table CS_AREA_LEVEL_DETAIL 407 rows exported

. . exporting table CS_ASSESS 222 rows exported

. . exporting table CS_ASSESS_CATALOG 29 rows exported

. . exporting table CS_ASSESS_MODEL 23 rows exported

. . exporting table CS_ASSESS_MODEL_REL 219 rows exported

. . exporting table CS_ASSESS_MODEL_REL_1120 194 rows exported

. . exporting table CS_ASSESS_TARGET 385 rows exported

. . exporting table CS_AUDIT_LOG 32 rows exported

. . exporting table CS_BASE_SALARY 46 rows exported

. . exporting table CS_CFG_CODE 75 rows exported

. . exporting table CS_CHANNEL_DEFINITION 5 rows exported

. . exporting table CS_FLOW 13 rows exported

. . exporting table CS_FLOW_DEPEND 16 rows exported

. . exporting table CS_GANGWEI_TARGET_REL 26 rows exported

. . exporting table CS_JOB 10 rows exported

. . exporting table CS_KH_STAFF_HR 0 rows exported

. . exporting table CS_LEAVE_WORD 0 rows exported

. . exporting table CS_MENU 39 rows exported

. . exporting table CS_OPERLOG 2740 rows exported

. . exporting table CS_PERFORMANCE_CURRENT 1575 rows exported

. . exporting table CS_QFHS_NCTBD_YB 0 rows exported

. . exporting table CS_QFHS_NCZJ_YB 0 rows exported

. . exporting table CS_RATED_PERSONNEL 46 rows exported

. . exporting table CS_RATED_PERSONNEL_20111122 46 rows exported

. . exporting table CS_ROLE 7 rows exported

. . exporting table CS_ROLE_MENU_REL 145 rows exported

. . exporting table CS_RULES 221 rows exported

. . exporting table CS_RULES_1120 186 rows exported

. . exporting table CS_RULES_20111123 227 rows exported

. . exporting table CS_SALARY_ADJUST 46 rows exported

. . exporting table CS_SALARY_AREA_ASSESS_CATALOGY 361 rows exported

. . exporting table CS_SALARY_AREA_ASSESS_TYPE 0 rows exported

. . exporting table CS_SALARY_AREA_SUM 82 rows exported

. . exporting table CS_SALARY_CHECK 0 rows exported

. . exporting table CS_SALARY_RESULT 158 rows exported

. . exporting table CS_STAFF 568 rows exported

. . exporting table CS_STAFFLOG 455 rows exported

. . exporting table CS_STAFF_AREA_NUM 0 rows exported

. . exporting table CS_STAFF_AREA_REL 142 rows exported

. . exporting table CS_STAFF_AREA_REL_1125 145 rows exported

. . exporting table CS_STAFF_AREA_REL_20111123 145 rows exported

. . exporting table CS_STAFF_AREA_REL_20111124 141 rows exported

. . exporting table CS_STAFF_AREA_REL_BAK 120 rows exported

. . exporting table CS_STAFF_BAK 567 rows exported

. . exporting table CS_STAFF_CHANNEL_REL 100 rows exported

. . exporting table CS_STAFF_CHANNEL_REL_BAK 49 rows exported

. . exporting table CS_STAFF_DEPARTMENT_REL 102 rows exported

. . exporting table CS_STAFF_FEED_BACK 2 rows exported

. . exporting table CS_STAFF_JOB_REL 46 rows exported

. . exporting table CS_STAFF_ROLE_REL 568 rows exported

. . exporting table CS_TABLE_DEF 217 rows exported

. . exporting table CS_TARGET_ASSIGN 1844 rows exported

. . exporting table CS_TARGET_ASSIGN_20111121 174 rows exported

. . exporting table CS_TARGET_ASSIGN_20111123 1206 rows exported

. . exporting table CS_TARGET_MODEL 17 rows exported

. . exporting table CS_TARGET_MODEL_REL 1844 rows exported

. . exporting table CS_TARGET_MODEL_REL_20111121 174 rows exported

. . exporting table CS_TARGET_MODEL_REL_20111122 1000 rows exported

. . exporting table CS_TARGET_MODEL_REL_20111123 1206 rows exported

. . exporting table CS_TASK 0 rows exported

. . exporting table CS_TASK_LOG 152 rows exported

. . exporting table CS_TGT_LIST_URL 0 rows exported

. . exporting table CS_VISITLOG 2544 rows exported

. . exporting table CS_WAGE_BASE 92 rows exported

. . exporting table CS_WAGE_BASE_AVG 83 rows exported

. . exporting table CS_WAGE_BASE_AVG_20111124 83 rows exported

. . exporting table DW_LOG 0 rows exported

. . exporting table GLOBAL_INFO 1 rows exported

. . exporting table GRID_CHECK 22 rows exported

. . exporting table GRID_MANAGER_CHECK_TAB 0 rows exported

. . exporting table GRID_TARGET_MODEL_TAB 17 rows exported

. . exporting table LOG_PROC_RUN 0 rows exported

. . exporting table PM_YBJF_TOTAL 0 rows exported

. . exporting table RPT_KPI_ARG 100 rows exported

. . exporting table RPT_SALARY_RESULT 46 rows exported

. . exporting table TAB_TEMP1 255 rows exported

. . exporting table TEMP_HANXF_TARGET_DOWN 176 rows exported

. . exporting table TEMP_HEY_TARGET_DOWN 555 rows exported

. . exporting table TEST_ZYP 589 rows exported

. . exporting table TMP_ZB_INFO 648 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

結果這次匯出就很正常，並且最後還顯示 without warnings。然後再次執行匯入

C:\Users\lnwxzyp>imp zyp/zyp@demo buffer=56000 file=D:\Work\sc_bat\bdump\bz.dmp

fromuser=bz_qudao touser=zyp

Import: Release 11.2.0.1.0 - Production on Sun Nov 27 18:12:15 2011

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by BZ_QUDAO, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. . importing table "COMMON_CUSTOMER" 0 rows imported

. . importing table "CS_AREA" 525 rows imported

IMP-00017: following statement failed with ORACLE error 1917:

"GRANT SELECT ON "CS_AREA" TO "ALL_QUDAO""

IMP-00003: ORACLE error 1917 encountered

ORA-01917: user or role 'ALL_QUDAO' does not exist

. . importing table "CS_AREA_BAK" 525 rows imported

. . importing table "CS_AREA_LEVEL_DETAIL" 407 rows imported

. . importing table "CS_ASSESS" 222 rows imported

. . importing table "CS_ASSESS_CATALOG" 29 rows imported

. . importing table "CS_ASSESS_MODEL" 23 rows imported

. . importing table "CS_ASSESS_MODEL_REL" 219 rows imported

. . importing table "CS_ASSESS_MODEL_REL_1120" 194 rows imported

. . importing table "CS_ASSESS_TARGET" 385 rows imported

. . importing table "CS_AUDIT_LOG" 32 rows imported

. . importing table "CS_BASE_SALARY" 46 rows imported

. . importing table "CS_CFG_CODE" 75 rows imported

. . importing table "CS_CHANNEL_DEFINITION" 5 rows imported

. . importing table "CS_FLOW" 13 rows imported

. . importing table "CS_FLOW_DEPEND" 16 rows imported

. . importing table "CS_GANGWEI_TARGET_REL" 26 rows imported

. . importing table "CS_JOB" 10 rows imported

. . importing table "CS_KH_STAFF_HR" 0 rows imported

. . importing table "CS_LEAVE_WORD" 0 rows imported

. . importing table "CS_MENU" 39 rows imported

. . importing table "CS_OPERLOG" 2740 rows imported

. . importing table "CS_PERFORMANCE_CURRENT" 1575 rows imported

. . importing table "CS_QFHS_NCTBD_YB" 0 rows imported

. . importing table "CS_QFHS_NCZJ_YB" 0 rows imported

. . importing table "CS_RATED_PERSONNEL" 46 rows imported

. . importing table "CS_RATED_PERSONNEL_20111122" 46 rows imported

. . importing table "CS_ROLE" 7 rows imported

. . importing table "CS_ROLE_MENU_REL" 145 rows imported

. . importing table "CS_RULES" 221 rows imported

. . importing table "CS_RULES_1120" 186 rows imported

. . importing table "CS_RULES_20111123" 227 rows imported

. . importing table "CS_SALARY_ADJUST" 46 rows imported

. . importing table "CS_SALARY_AREA_ASSESS_CATALOGY" 361 rows imported

. . importing table "CS_SALARY_AREA_ASSESS_TYPE" 0 rows imported

. . importing table "CS_SALARY_AREA_SUM" 82 rows imported

. . importing table "CS_SALARY_CHECK" 0 rows imported

. . importing table "CS_SALARY_RESULT" 158 rows imported

. . importing table "CS_STAFF" 568 rows imported

IMP-00017: following statement failed with ORACLE error 1917:

"GRANT SELECT ON "CS_STAFF" TO "ALL_QUDAO""

IMP-00003: ORACLE error 1917 encountered

ORA-01917: user or role 'ALL_QUDAO' does not exist

. . importing table "CS_STAFFLOG" 455 rows imported

. . importing table "CS_STAFF_AREA_NUM" 0 rows imported

. . importing table "CS_STAFF_AREA_REL" 142 rows imported

IMP-00017: following statement failed with ORACLE error 1917:

"GRANT SELECT ON "CS_STAFF_AREA_REL" TO "ALL_QUDAO""

IMP-00003: ORACLE error 1917 encountered

ORA-01917: user or role 'ALL_QUDAO' does not exist

. . importing table "CS_STAFF_AREA_REL_1125" 145 rows imported

. . importing table "CS_STAFF_AREA_REL_20111123" 145 rows imported

. . importing table "CS_STAFF_AREA_REL_20111124" 141 rows imported

. . importing table "CS_STAFF_AREA_REL_BAK" 120 rows imported

. . importing table "CS_STAFF_BAK" 567 rows imported

. . importing table "CS_STAFF_CHANNEL_REL" 100 rows imported

. . importing table "CS_STAFF_CHANNEL_REL_BAK" 49 rows imported

. . importing table "CS_STAFF_DEPARTMENT_REL" 102 rows imported

. . importing table "CS_STAFF_FEED_BACK" 2 rows imported

. . importing table "CS_STAFF_JOB_REL" 46 rows imported

. . importing table "CS_STAFF_ROLE_REL" 568 rows imported

. . importing table "CS_TABLE_DEF" 217 rows imported

. . importing table "CS_TARGET_ASSIGN" 1844 rows imported

. . importing table "CS_TARGET_ASSIGN_20111121" 174 rows imported

. . importing table "CS_TARGET_ASSIGN_20111123" 1206 rows imported

. . importing table "CS_TARGET_MODEL" 17 rows imported

. . importing table "CS_TARGET_MODEL_REL" 1844 rows imported

. . importing table "CS_TARGET_MODEL_REL_20111121" 174 rows imported

. . importing table "CS_TARGET_MODEL_REL_20111122" 1000 rows imported

. . importing table "CS_TARGET_MODEL_REL_20111123" 1206 rows imported

. . importing table "CS_TASK" 0 rows imported

. . importing table "CS_TASK_LOG" 152 rows imported

. . importing table "CS_TGT_LIST_URL" 0 rows imported

. . importing table "CS_VISITLOG" 2544 rows imported

. . importing table "CS_WAGE_BASE" 92 rows imported

. . importing table "CS_WAGE_BASE_AVG" 83 rows imported

. . importing table "CS_WAGE_BASE_AVG_20111124" 83 rows imported

. . importing table "DW_LOG" 0 rows imported

. . importing table "GLOBAL_INFO" 1 rows imported

. . importing table "GRID_CHECK" 22 rows imported

. . importing table "GRID_MANAGER_CHECK_TAB" 0 rows imported

. . importing table "GRID_TARGET_MODEL_TAB" 17 rows imported

. . importing table "LOG_PROC_RUN" 0 rows imported

. . importing table "PM_YBJF_TOTAL" 0 rows imported

. . importing table "RPT_KPI_ARG" 100 rows imported

. . importing table "RPT_SALARY_RESULT" 46 rows imported

. . importing table "TAB_TEMP1" 255 rows imported

. . importing table "TEMP_HANXF_TARGET_DOWN" 176 rows imported

. . importing table "TEMP_HEY_TARGET_DOWN" 555 rows imported

. . importing table "TEST_ZYP" 589 rows imported

. . importing table "TMP_ZB_INFO" 648 rows imported

About to enable constraints...

Import terminated successfully with warnings.

這裡雖然是出現了一些錯誤，但主要是由於我本機資料庫上缺少一些物件造成的，登陸本機資料庫後檢視發現中文註釋果然就正常了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12216142/viewspace-712176/，如需轉載，請註明出處，否則將追究法律責任。

mysql客戶端連線的幾個常見錯誤
2015-04-28
MySql客戶端
Oracle exp 設定客戶端字符集
2014-02-13
Oracle客戶端
錯誤：System.Data.OracleClient 需要 Oracle 客戶端軟體 8.1.7 或更高版本問題
2024-04-19
Oracleclient客戶端
flutter版本的玩Android客戶端
2018-12-21
FlutterAndroid客戶端
禁止客戶端誤 truncate table
2011-10-31
客戶端
ChromeDriver版本差異
2017-04-05
Chrome
[jaeger] 二、客戶端使用 (Java版本)
2019-05-06
客戶端Java
jquery版本中的差異
2014-12-25
jQuery
禁止客戶端誤刪除tablespace
2011-10-31
客戶端
Oracle客戶端非預設路徑安裝出現的錯誤
2011-09-01
Oracle客戶端
WCF服務端丟擲的異常會跑到客戶端
2013-10-15
服務端客戶端
IMP-00003: 遇到 ORACLE 錯誤 20001,版本差異引起的問題
2009-07-15
Oracle
rsync 客戶端同步的時候報錯
2008-05-15
客戶端
專案分享九：客戶端的異常處理
2015-12-27
客戶端
Oracle跨版本匯出EXP-00003錯誤的解決（）
2010-07-08
Oracle
微服務架構，客戶端如何catch服務端的異常？
2024-03-06
微服務架構客戶端服務端
Flutter 新聞客戶端 - 12 採用 sentry 平臺收集錯誤
2020-06-24
Flutter客戶端
WebSphere Adapter 客戶定製的錯誤繫結
2010-08-30
WebAPT
SonicWALL Global VPN客戶端連線出現Failed to open the IPSec driver錯誤
2011-05-23
客戶端AI
WCF 關閉預設除錯客戶端
2017-06-17
除錯客戶端
Beego當客戶端cookie為空時報錯
2017-11-24
Go客戶端Cookie
WPF 通過程式實現異常隔離的客戶端
2021-09-17
客戶端
《samba搭建win客戶端和linux客戶端的區別》
2014-06-07
Samba客戶端Linux
dubbo客戶端
2018-11-07
客戶端
Pulsar客戶端
2018-12-03
客戶端
mqtt 客戶端
2020-09-25
MQQT客戶端
ora-01031錯誤(在客戶端以SYS使用者登陸報錯)
2008-07-22
客戶端
資料庫主機名錯誤導致客戶端TNS-12541
2017-11-02
資料庫客戶端
客戶端TNSPING通連線出現ORA-12514錯誤
2011-10-28
客戶端
版本不當導致的exp出錯
2008-10-27
MQTTJava客戶端的使用
2018-02-28
MQQTJava客戶端
redis客戶端的使用
2017-03-18
Redis客戶端
IE客戶客戶端程式開發的利器Bindows
2003-10-12
客戶端
Nacos - 客戶端心跳續約及客戶端總結
2021-01-04
客戶端
超完整的 Chrome 瀏覽器客戶端除錯大全
2016-12-07
Chrome瀏覽器客戶端除錯
【軟體差錯警示鐘】一起軟體差錯造成的多名病人死亡事故
2012-02-28
安卓客戶端和伺服器端的通訊（勘誤填坑版）
2017-10-24
安卓客戶端伺服器
angular-resource版本差異問題
2014-10-10
Angular

EXP客戶端版本差異造成的錯誤

相關文章