exp/imp来避开ORA-31644错误

系统中有个13G以上的大表，使用expdp/impdp工具时遇到了RA-31644错误，网上也找不到满意的答案，最终使用exp/imp时发现导入成功，具体命令如下：

[@more@]

expdp system/system@test1 DIRECTORY=DUMPDIR DUMPFILE=HD_EXPEND_111028.dmp tables=(REP.HD_EXPEND) content=all LOGFILE=exp1028_REP.log;

--通过SCP命令导出到报表服务器，扩展需要导入数据的表空间：

alter tablespace rep add datafile '/data/oradata/rep_08.dbf' size 6000M

--使用impdp进行导入：

impdp system/system@test2 DIRECTORY=IMPDIR tables=(HD_ITV_EXPEND) DUMPFILE=HD_ITV_EXPEND_111028.dmp REMAP_SCHEMA=REP:REP logfile=IMP1028_REP.log;

但是报如下错误：

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORA-39002: invalid operation

ORA-31694: master table "SYSTEM"."SYS_IMPORT_TABLE_01" failed to load/unload

ORA-31644: unable to position to block number 3294406 in dump file "/data/backup/HD_EXPEND_111028.dmp"

因找不到该错误的处理方法，只好使用exp/imp工具：

exp system/system@test1 file=/oracle/repdmp/rep_20111101.dmp tables=REP.HD_EXPEND log=/oracle/repdmp/rep_bak.log direct=y statistics=none buffer=20480000 recordlength=65534

Export: Release 10.2.0.5.0 - Production on Tue Nov 1 13:33:05 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, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Direct Path ...

Current user changed to REP

. . exporting table HD_EXPEND 99394806 rows exported

Export terminated successfully without warnings.

--通过SCP命令导出到报表服务器并进行导入

imp system/system fromuser=rep touser=rep tables=HD_EXPEND file =/data/backup/rep_20111101.dmp log=/data/backup/imp_rep.log

Import: Release 10.2.0.1.0 - Production on Tue Nov 1 15:10:14 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

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

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

export client uses US7ASCII character set (possible charset conversion)

. importing REP's objects into REP

. . importing table "HD_EXPEND" 99394806 rows imported

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

"GRANT SELECT ON "HD_ITV_EXPEND" TO "aaa""

…...

Import terminated successfully with warnings.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18841027/viewspace-1056085/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18841027/viewspace-1056085/