标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,597)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
标签归档:DIRECT=Y
增加默认值列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