如果一个表包含了触发器，在通过IMP导入数据的时候，原本的EMPTY_LOB将被转化为NULL，以提高IMP的效率。

Oracle 10以后的版本都存在这个现象：

SQL> create table t_lob (id number, content clob);

Table created.

SQL> insert into t_lob values (1, empty_clob());

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_lob where content is null;

no rows selected

下面通过exp和expdp导出表：

[oracle@yans1 ~]$ exp test/test file=t_lob.dmp tables=t_lob

Export: Release 10.2.0.3.0 - Production on 星期六 12月 19 22:48:48 2009

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table T_LOB 1 rows exported
Export terminated successfully without warnings.
[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_lob.dp tables=t_lob

Export: Release 10.2.0.3.0 - 64bit Production on 星期六, 19 12月, 2009 22:49:07

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_lob.dp tables=t_lob
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."T_LOB" 5.257 KB 1 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_lob.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 22:49:36

下面删除T_LOB表：

SQL> drop table t_lob purge;

Table dropped.

利用IMP导入：

[oracle@yans1 ~]$ imp test/test file=t_lob.dmp full=y

Import: Release 10.2.0.3.0 - Production on 星期六 12月 19 22:50:20 2009

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table "T_LOB" 1 rows imported
Import terminated successfully without warnings.

检查导入的LOB列是否为空：

SQL> select * from t_lob where content is null;

no rows selected

下面再次删除T_LOB表：

SQL> drop table t_lob purge;

Table dropped.

利用IMPDP导入：

[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_lob.dp

Import: Release 10.2.0.3.0 - 64bit Production on 星期六, 19 12月, 2009 22:51:26

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=d_output dumpfile=t_lob.dp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_LOB" 5.257 KB 1 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 22:51:28

检查导入的LOB列是否为空：

SQL> select * from t_lob where content is null;

no rows selected

可以看到，无论是IMP还是IMPDP，在没有触发器的情况下，导入的都是EMPTY_LOB，而不是NULL，下面添加一个触发器：

SQL> create trigger t_lob
2 before insert on t_lob
3 for each row
4 begin
5 null;
6 end;
7 /

Trigger created.

SQL> delete t_lob;

1 row deleted.

SQL> commit;

Commit complete.

通过IMP执行导入，设置IGNORE=Y：

[oracle@yans1 ~]$ imp test/test file=t_lob.dmp full=y ignore=y

Import: Release 10.2.0.3.0 - Production on 星期六 12月 19 23:34:34 2009

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table "T_LOB" 1 rows imported
Import terminated successfully without warnings.

检查导入的LOB内容：

SQL> select * from t_lob where content is null;

ID CONTENT
---------- --------------------------------------------------------------------------------
1

显然IMP将EMPTY_LOB转变为NULL了。

下面看看数据泵的导入是否存在同样的问题：

SQL> delete t_lob;

1 row deleted.

利用IMPDP导入数据：

[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_lob.dp content=data_only

Import: Release 10.2.0.3.0 - 64bit Production on 星期六, 19 12月, 2009 23:54:56

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=d_output dumpfile=t_lob.dp content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_LOB" 5.257 KB 1 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 23:55:00

检查导入的LOB数据是否为空：

SQL> select * from t_lob where content is null;

no rows selected

显然这个问题只是IMP会出现。显然，EMPTY_LOB和NULL还是有区别的，如果程序访问的时候认为二者是不同的状态，那么在使用IMP导入LOB数据的时候要检查表上是否存在触发器，否则很可能导致表中原始的EMPTY_LOB变成NULL。

ITPub博客

包含触发器的LOB表执行IMP导致EMPTY_LOB变为空

最新文章