首页 > 其他 > 详细

EXP-00091和IMP-00010解决办法

时间:2014-03-27 20:29:34      阅读:589      评论:0      收藏:0      [点我收藏+]

 

在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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                      COUNTRIES         23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@node2 ~]$ 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.
[oracle@node2 ~]$ exit
exit

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                      COUNTRIES         23 rows exported
Export terminated successfully without warnings.

[oracle@node2 ~]$ ll
total 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:22 COUNTRIES.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 ~]$ ll
total 32
-rw-r--r-- 1 oracle oinstall 24576 Mar 27 15:26 COUNTRIES.dmp
drwxr-xr-x 2 oracle oinstall  4096 Mar 27 12:39 script
drwxr-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: sh
Password: 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With 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 verification
IMP-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.00
USH


再次导入,成功:

[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: sh
Password: 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With 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 path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
List 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 user
Enter table(T) or partition(T:P) name or . if done: 

. importing SH‘s objects into SH
IMP-00008: unrecognized statement in the export file: 
  
. importing SH‘s objects into SH
. . importing table                    "COUNTRIES"         23 rows imported
Import terminated successfully with warnings.
[oracle@ocm1 ~]$ 


SYS@PROD> conn sh/sh
Connected.
SH@PROD> desc COUNTRIES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL NUMBER
 COUNTRY_ISO_CODE                          NOT NULL CHAR(2)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 COUNTRY_SUBREGION                         NOT NULL VARCHAR2(30)
 COUNTRY_SUBREGION_ID                      NOT NULL NUMBER
 COUNTRY_REGION                            NOT NULL VARCHAR2(20)
 COUNTRY_REGION_ID                         NOT NULL NUMBER
 COUNTRY_TOTAL                             NOT NULL VARCHAR2(11)
 COUNTRY_TOTAL_ID                          NOT NULL NUMBER
 COUNTRY_NAME_HIST                                  VARCHAR2(40)

SH@PROD> 
SH@PROD> select count(*) from COUNTRIES;

  COUNT(*)
----------
        23
 

EXP-00091和IMP-00010解决办法,布布扣,bubuko.com

EXP-00091和IMP-00010解决办法

原文:http://blog.csdn.net/rlhua/article/details/22295689

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!