如果一個表包含了觸發器，在透過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。

包含觸發器的LOB表執行IMP導致EMPTY_LOB變為空

相關文章