问题 : ORA-1400 During Import of Export Dump Written in Direct Path Mode

转载 2012年03月23日 17:09:14
connect test/test

drop table a_tab purge;

#> imp test/test file=a_tab.dmp tables=a_tab

Import: Release 11.1.0.7.0 - Production on Mon May 18 17:19:21 2009

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table "A_TAB"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("TEST"."A_TAB"."COL001")
Column : 1
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("TEST"."A_TAB"."COL001")
Column : 2
Column : 0 rows imported
Import terminated successfully with warnings.

Cause

The root cause of this behavior is the new feature introduced with 11gR1:

- the columns with property 1073741824 (see the column value PROPERTY in COL$ for column COL001) are columns with defaults that are not saved in the column. This means on disk, the null values for this column must be replaced by its default obtained from the column definition:

connect / as sysdba
select col#, name, property
from   col$
where  obj# in (select object_id
                from   dba_objects
                where  owner = 'TEST' and
                       object_name = 'A_TAB');

COL#       NAME                           PROPERTY
---------- ------------------------------ ----------
         1 ID                                      0
         2 COL001                         1073741824

2 rows selected.

This mechanism doesn't work properly when direct path is used with original export (the value is not replaced by its default but the NULL values is stored in export dump. This will produce ORA-1400 during import).

Original export (exp)is desupported in 11g so there is no opened bug against this product.

Solution

1. Use DataPump export/import utilities (expdp/impdp)

or:

2. Start traditional export utility (exp) in conventional path mode (DIRECT=N).


如下重新导出:

exp system/oracle@IODAC file=DEV_IODAC_`date +"%Y%m%d%H%M%S"`.dmp log=DEV_IODAC_`date +"%Y%m%d%H%M%S"`.log buffer=40960000 direct=N recordlength=65535 consistent=y full=y

再导入就不再出现错误了

Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Da

  Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading ...
  • tianlesoftware
  • tianlesoftware
  • 2010年12月22日 02:12
  • 15798

Android import和export使用说明 及 export报错:Problems were encountered during export解决

在Android开发export项目时发现有时会报错,内容如下: Problems were encountered during export:   Error exporting RectPho...
  • u014686356
  • u014686356
  • 2015年04月02日 11:23
  • 1646

【故障处理】imp-00051,imp-00008

【故障处理】imp-00051,imp-00008 1.1  BLOG文档结构图   1.2  故障分析及解决过程 imp导入报错: IMP-00...
  • lihuarongaini
  • lihuarongaini
  • 2017年01月08日 11:45
  • 999

Parameter DIRECT: Conventional Path Export Versus Direct Path Export

Modified 24-JUN-2010     Type BULLETIN     Status PUBLISHED In this Document  Purpose  Scope and App...
  • laichangbo
  • laichangbo
  • 2010年12月26日 00:58
  • 502

【Oracle】数据导入导出(expdp、impdp ;export、import)——用户模式

针对Oracle11g数据库中用户的数据备份和恢复而进行的相关导入导出操作。
  • Shmily_91
  • Shmily_91
  • 2016年04月02日 18:21
  • 2168

产生crashdump的三种方法

转载:http://blog.yufeng.info/archives/2737 crashdump对于erlang的系统来讲如同core对于c/++程序一样宝贵,对于系统问题的修复...
  • zhangxinrun
  • zhangxinrun
  • 2013年06月22日 12:37
  • 6748

【DB.Oracle】export/import dmp 文件

java.lang.Class 对象 getdeclaredfileds(), 对每个filed,获取annotation
  • robinjwong
  • robinjwong
  • 2014年10月29日 20:23
  • 2246

ORA-39776: fatal Direct Path API error loading table AB.PL_SCHED

数据采集入库的时候出现这个问题,后来把表删除重建就好了,不知啥原因?...
  • terryhuang
  • terryhuang
  • 2009年12月25日 10:47
  • 2971

【Oracle】数据迁移工具(2):Data Dump

Data Dump 使用命令行IMPDP/EXPDP实现导入导出表、schema、表空间及数据库。IMPDP/EXPDP命令行中可以加入以下选项,来实现更细粒度的导入导出。 IMPDP/EXPDP和I...
  • NextAction
  • NextAction
  • 2017年01月18日 15:08
  • 1140

Sqoop-1.4.4工具import和export使用详解

Sqoop可以在HDFS/Hive和关系型数据库之间进行数据的导入导出,其中主要使用了import和export这两个工具。这两个工具非常强大,提供了很多选项帮助我们完成数据的迁移和同步。比如,下面两...
  • wangmuming
  • wangmuming
  • 2014年05月08日 14:24
  • 30298
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:问题 : ORA-1400 During Import of Export Dump Written in Direct Path Mode
举报原因:
原因补充:

(最多只允许输入30个字)