ITPub博客

首页 > Linux操作系统 > Linux操作系统 > EXP客户端版本差异造成的错误

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

原创 Linux操作系统 作者:lnwxzyp 时间:2011-11-27 16:31:28 0 删除 编辑
    今天将正准备上线的项目数据库的用户下的全部数据导来,一来进行备份;二者也导入到自己笔记本的数据库当中进行测试。结果发现用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

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-04-25

  • 博文量
    129
  • 访问量
    711113