EXP客户端版本差异造成的错误

最新推荐文章于 2021-04-06 15:32:50 发布

congdulu5128

最新推荐文章于 2021-04-06 15:32:50 发布

阅读量172

点赞数

文章标签：数据库

今天将正准备上线的项目数据库的用户下的全部数据导来，一来进行备份；二者也导入到自己笔记本的数据库当中进行测试。结果发现用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\b dump\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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12216142/viewspace-712176/

congdulu5128

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
EXP客户端版本差异造成的错误

今天将正准备上线的项目数据库的用户下的全部数据导来，一来进行备份；二者也导入到自己笔记本的数据库当中进行测试。结果发现用11.2.0.1.0的客户端来远程导出10.2.0.5.0版本的数据就...
复制链接

扫一扫