EXP-00104: datatype (BINARY_DOUBLE) 错误
惜分飞发表于 2015-10-14 16:16:02
联系：手机(13429648788) QQ(107644445)
标题：EXP-00104: datatype (BINARY_DOUBLE) 错误
作者：惜分飞©版权所有[未经本人同意,请不得以任何形式转载,否则有进一步追究法律责任的权利.]
进行了一次使用exp数据迁移,客户和我说丢失了表,我觉得比较奇怪,分析日志发现EXP-00104: datatype (BINARY_DOUBLE)错误,对其单表进行导出,发现问题重现
[oracle@app73 ~]$ exp system/oracle123 file=/Data/1.dmp tables=CHF.T_XIFENFEI Export: Release 11.1.0.6.0 - Production on Wed Oct 14 23:25:31 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.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 About to export specified tables via Conventional Path ... Current user changed to CHF . . exporting table T_XIFENFEI EXP-00104: datatype (BINARY_DOUBLE) of column NUMCOMMISSIONRATE in table CHF.T_XIFENFEI is not supported, table will not be exported
数据库版本
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for Linux: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production
表结构
SQL> DESC CHF.T_XIFENFEI Name Null? Type ----------------------------------------- -------- ---------------------------- ………… NUMPRO NUMBER NUMCOMMISSIONRATE BINARY_DOUBLE NUMSEQ BINARY_DOUBLE NUMMARK NUMBER …………
解决办法,使用expdp/impdp代替exp/imp
[oracle@app73 ~]$ expdp system/manager dumpfile=exp.dmp tables=CHF.T_XIFENFEI directory=exp_dir Export: Release 11.1.0.6.0 - 64bit Production on Wednesday, 14 October, 2015 23:30:38 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01":system/******** dumpfile=exp.dmp tables=CHF.T_XIFENFEI directory=exp_dir Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 959 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "CHF"."T_XIFENFEI" 814.7 MB 1601202 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /tmp/exp.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:31:31 [oracle@app101-2 Data]$ impdp system/oracle dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir Import: Release 11.2.0.4.0 - Production on Wed Oct 14 23:44:35 2015 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 - 64bit Production With the Partitioning and Data Mining options Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TABLE_01":system/******** dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "CHF"."T_XIFENFEI" 814.7 MB 1601202 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 14 23:45:37 2015 elapsed 0 00:01:02
出现此类错误,也和自己的粗心有关系,没有仔细检查导出日志,另外该问题从10.1.0.2就有了,直到现在才发现,证明知识点欠缺.同样对于binary_float也有一样问题.
具体可以参考：Export Failed Due To EXP-104 Error, Datatype (Binary_double) Is Not Supported [ID 421591.1]