Exp direct 方式的又一个坑

原创心在梦在 2023-08-11

1776

Exp Direct 方式的又一个坑

背景

我们之前在《Oracle 如何给大表添加字段，并带有默认值》文章中介绍过，从11g开始，如果增加的列符合default+not null这两个条件的话，它只会去更新数据字典，不会去更新物理行，所以操作可以很短的时间完成，且不会对生产产生影响。从Oracle 12c更进一步，允许元数据默认值not null 可选，不管该列是否被定义not null，添加字段带默认值都很快。

exp 参数direct =y 表示直接路径导出，，数据从磁盘读入到高速缓存，直接写入到最终文件，比普通路径导出速度快很多。但是因为没有数据行检查与匹配的过程，所以也存在一些限制，比如：不支持query子句，不支持带Blob类型字段的表，导出一个带坏块的数据库时,不会报警等。

那么，对于这种类型的表，在exp导出时候，有什么注意点？

实验

一、11G 环境下

1. 创建测试表

SQL> conn sxc/sxc
Connected.

SQL> create table test1 as select * from tab;

Table created.

SQL> create table test2 as select * from tab;

Table created.

SQL> alter table test1 add a varchar2(100) default 'aa';

Table altered.

SQL> alter table test2 add b varchar2(100) default 'bb' not null;

Table altered.

SQL> exit

2. 使用exp + direct=y 导出

-- exp test1表
[oracle@ora11204 ~]$ exp sxc/sxc file=test1.dmp tables=test1 direct=y
Export: Release 11.2.0.4.0 - Production on Fri Aug 11 03:10:35 2023
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 ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path ...
. . exporting table                          TEST1         22 rows exported
Export terminated successfully without warnings.

-- exp test2表
[oracle@ora11204 ~]$ exp sxc/sxc file=test2.dmp tables=test2 direct=y
Export: Release 11.2.0.4.0 - Production on Fri Aug 11 03:10:44 2023
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 ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path ...
. . exporting table                          TEST2         23 rows exported
Export terminated successfully without warnings.

结论：test1、test2 表导出成功，无任何报错。

3. 使用imp 导入

--1) 删除测试表
SQL> drop table test1 purge;

Table dropped.

SQL> drop table test2 purge;

Table dropped.

SQL> exit

--2) imp 导入
[oracle@ora11204 ~]$ imp sxc/sxc file=test1.dmp tables=test1
Import: Release 11.2.0.4.0 - Production on Fri Aug 11 03:11:38 2023
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
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SXC's objects into SXC
. importing SXC's objects into SXC
. . importing table                        "TEST1"         22 rows imported
Import terminated successfully without warnings.


[oracle@ora11204 ~]$ imp sxc/sxc file=test2.dmp tables=test2
Import: Release 11.2.0.4.0 - Production on Fri Aug 11 03:11:50 2023
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
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SXC's objects into SXC
. importing SXC's objects into SXC
. . importing table                        "TEST2"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SXC"."TEST2"."B")
...

结论：test1 导入成功，test2 导入失败，抛出ORA-01400错误，原因就是11g的新特性，对表新增非空且有缺省值列的时候，ORACLE不会全表更新，只是改了数据字典。exp 如果加了参数direct=y导致了丢失了字段值，不会导出字段的缺省值，所以报错了。需要使用expdp 或者exp direct=n 的方式导出。

参考文档：Doc ID 826746.1
图片.png

二、19G 环境下

1. 创建测试表

SQL> conn sxc/sxc
Connected.

SQL> create table test3 as select * from tab;

Table created.

SQL> create table test4 as select * from tab;

Table created.

SQL> alter table test3 add a varchar2(100) default 'aa';

Table altered.

SQL> alter table test4 add b varchar2(100) default 'bb' not null;

Table altered.

SQL> exit

2. 使用exp + direct=y 导出

-- exp test3表
[oracle@ora19c ~]$ exp sxc/sxc file=test3.dmp tables=test3 direct=y
Export: Release 19.0.0.0.0 - Production on Fri Aug 11 13:18:22 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path ...
. . exporting table                          TEST3
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01426: numeric overflow
Export terminated successfully with warnings.

-- exp test4表
[oracle@ora19c ~]$ exp sxc/sxc file=test4.dmp tables=test4 direct=y
Export: Release 19.0.0.0.0 - Production on Fri Aug 11 13:19:29 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path ...
. . exporting table                          TEST4         10 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

结论：test3 导出失败，抛出ORA-01426: numeric overflow错误。test4 导出正常。

图片.png

从exp trace 文件中，可以看到是内部执行select type#, property from col$ where obj#=:1 order by segcol# 语句时候，发生了ORA-01426: numeric overflow错误，暂时未找到相关解释，应该也是和12c 新特性相关，我们先使用 exp direct=n 和 expdp 导出试试。

--1) exp direct=n 方式：数据已经正常导出，但是导出statistics时候不报错，抛出 ORA-01455错误信息，但是不影响导入，加上 statistics=none 参数即可。
[oracle@ora19c ~]$ exp sxc/sxc file=test3_2.dmp tables=test3
Export: Release 19.0.0.0.0 - Production on Fri Aug 11 13:48:49 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                          TEST3         11 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
Export terminated successfully with warnings.

--2） expdp 方式：导出正常，无任何报错
[oracle@ora19c dmp]$ expdp sxc/sxc dumpfile=test3_expdp.dmp  directory=DIR_EXP logfile=test3_expdp.log tables=test3
Export: Release 19.0.0.0.0 - Production on Fri Aug 11 13:50:25 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SXC"."SYS_EXPORT_TABLE_01":  sxc/******** dumpfile=test3_expdp.dmp directory=DIR_EXP logfile=test3_expdp.log tables=test3
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TEST3"                               6.539 KB      11 rows
Master table "SXC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dmp/test3_expdp.dmp
Job "SXC"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 11 13:50:54 2023 elapsed 0 00:00:26

3. 使用imp 导入

-- exp+direct=y方式，导出test3失败了，所以我们这里只测试test4导入
SQL> drop table test4 purge;

Table dropped.


[oracle@ora19c ~]$ imp sxc/sxc file=test4.dmp tables=test4
Import: Release 19.0.0.0.0 - Production on Fri Aug 11 13:24:43 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Export file created by EXPORT:V19.00.00 via direct path
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403:

Warning: This import generated a separate SQL file "import_sys" which contains DDL that failed due to a privilege issue.

. importing SXC's objects into SXC
. importing SXC's objects into SXC
. . importing table                        "TEST4"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SXC"."TEST4"."B")
....

结论：很遗憾，跟11g 一样，导入失败，同样的报错，需要使用expdp 或者exp direct=n 的方式导出。

结论

11g 环境下，使用 exp+direct=y导出表都没有问题，19c 环境下，导出default + not null 的表正常，导出仅default 的表报错。
11g 和 19c 环境下，使用 exp+direct=y方式导出的dmp，imp导入时候均报错。
在11g 以上环境下，使用exp+direct=y 方式导出，需要更加小心，如果是备份表，最好是找环境测试一下导入，确认无问题后，再删除源表，避免数据丢失。整体看起来，还是使用expdp 方式更加保险。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

Exp direct 方式的又一个坑

Exp Direct 方式的又一个坑

背景

实验

一、11G 环境下

1. 创建测试表

2. 使用exp + direct=y 导出

3. 使用imp 导入

二、19G 环境下

1. 创建测试表

2. 使用exp + direct=y 导出

3. 使用imp 导入

结论

评论