-
3D Cloud
asm mount asm恢复 bbed bootstrap$ dmp损坏 dul In Memory kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01190 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kccpb_sanity_check_2 ORA-15042 ORACLE 12C oracle dul ORACLE PATCH oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 redo异常 undo异常 YOUR FILES ARE ENCRYPTED _ALLOW_RESETLOGS_CORRUPTION 勒索恢复 数据库恢复 比特币 比特币 oracle 比特币加密 比特币勒索
文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (87)
- 数据库 (1,313)
- DB2 (22)
- MySQL (56)
- Oracle (1,199)
- Data Guard (39)
- EXADATA (7)
- GoldenGate (19)
- ORA-xxxxx (145)
- ORACLE 12C (71)
- ORACLE 18C (6)
- ORACLE 19C (8)
- Oracle ASM (56)
- Oracle Bug (7)
- Oracle RAC (40)
- Oracle 安全 (6)
- Oracle 开发 (25)
- Oracle 监听 (26)
- Oracle备份恢复 (392)
- Oracle安装升级 (54)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (42)
- PostgreSQL (10)
- SQL Server (26)
- SQL Server恢复 (7)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (23)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (6)
-
最近发表
- Alpha865qqz.id 加密数据库恢复
- incaseformat 病毒删除文件恢复
- xfs文件系统mysql删库恢复
- 对恢复案例:因对工作调整不满,链家一员工删除公司 9 TB数据:被判7年事件有感
- Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障
- Oracle Recovery Tools 12月份更新
- -bash: /bin/rm: Argument list too long
- ORA-27303: failure occurred at: skgpwinit6
- ORA-600 kffmLoad_1 kffmVerify_4
- ORA-00600 kfrHtAdd01
- ORA-00600 [2662]和ORA-00600 [4194]恢复
- 记录oracle安装的两个小问题(INS-30060和弹出子窗口异常)
- dblink会话引起library cache lock
- asm磁盘类似_DROPPED_0001_DATA名称故障处理
- 12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
- 再次遇到ORA-600 kokasgi1故障恢复
- sysaux表空间不足—WRH$_ACTIVE_SESSION_HISTORY
- sql server 删除数据库恢复
- .eight加密数据库恢复
- xiaolinghelper@firemail.cc加密数据库恢复
友情链接
标签归档:ORA-01400
增加默认值列exp DIRECT=Y导出,导入遭遇ORA-01400
最近的一次数据迁移中使用exp导出遭遇到ORA-01400: cannot insert NULL into 错误,凭着经验对其进行了重现,确定是由于增加默认值的列,使用DIRECT=Y 导出导致该问题
创建测试表
创建一张表,并且增加带默认值而且不为空的列,然后使用exp DIRECT=Y 导出数据
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> create table t_xifenfei as select * from dba_objects; Table created. SQL> alter table t_xifenfei add (c_xifenfei varchar2(30) default 'www.xifenfei.com' not null); Table altered. SQL> select c_xifenfei from t_xifenfei where rownum<10; C_XIFENFEI ------------------------------ www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com 9 rows selected. [oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000 COMPRESS=NO \ > BUFFER=102400000 STATISTICS=none DIRECT=Y recordlength=65535 file=/tmp/t_xifenfei.dmp Export: Release 11.2.0.4.0 - Production on Tue Aug 21 15:56:52 2018 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, 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 ... . . exporting table T_XIFENFEI ........ 86421 rows exported Export terminated successfully without warnings.
测试导入数据
导入到另外一个用户下面
[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei.dmp fromuser=xff touser=xff1 Import: Release 11.2.0.4.0 - Production on Tue Aug 21 15:57:53 2018 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, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via direct path Warning: the objects were exported by XFF, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing XFF's objects into XFF1 . . importing table "T_XIFENFEI" IMP-00019: row rejected due to ORACLE error 1400 IMP-00003: ORACLE error 1400 encountered ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI") Column : SYS Column : ICOL$ Column : Column : 20 Column : 2 Column : TABLE Column : 24-AUG-2013:11:37:35 Column : 24-AUG-2013:11:47:37 Column : 2013-08-24:11:37:35 Column : VALID Column : N Column : N Column : N Column : 1 Column : Column : IMP-00019: row rejected due to ORACLE error 1400 IMP-00003: ORACLE error 1400 encountered ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI")
这里可以看到,故障已经重现ORA-01400: cannot insert NULL into,无法将空值插入到刚刚新加的默认值的列中
使用常规导出
[oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000 COMPRESS=NO BUFFER=102400000 STATISTICS=none file=/tmp/t_xifenfei1.dmp Export: Release 11.2.0.4.0 - Production on Tue Aug 21 16:00:50 2018 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, 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 Conventional Path ... . . exporting table T_XIFENFEI ........ 86421 rows exported Export terminated successfully without warnings.
导入数据
[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei1.dmp fromuser=xff touser=xff1 Import: Release 11.2.0.4.0 - Production on Tue Aug 21 16:04:47 2018 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, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path Warning: the objects were exported by XFF, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing XFF's objects into XFF1 . . importing table "T_XIFENFEI" 86421 rows imported Import terminated successfully without warnings. [oracle@bogon ~]$ [oracle@bogon ~]$ sqlplus xff1/oracle SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 21 16:10:16 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select c_xifenfei from t_xifenfei where rownum<10; C_XIFENFEI ------------------------------ www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com www.xifenfei.com 9 rows selected.
通过测试证明,对于11g新特性增加默认值,而且非为空的列,如果使用exp常规方法导入,再导出没有任何问题,如果使用DIRECT=Y特性,将导致导入失败(ORA-01400).这是由于11g的快速增加列和默认值且不为空的新特性导致的(直接修改字典,并没有真正的去修改数据底层存储).
查询mos发现匹配bug: Bug 13961541 : EXP WITH DIRECT=Y CAN NOT HANDLE ADD COLUMN DEFAULT NOT NULL FEATURE IN 11G