联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
使用过dul的人都知道，dul在处理clob字段的时候，经常性出现乱码，而且官方没有提供好的处理方法，通过不断的测试折腾终于在2013年春节解决了2012年困惑在我心中的疑惑，不再因为dul不可以处理clob而觉得odu/aul更加高级。这个算是2013年给自己的第一份ORACLE数据库恢复方面大礼包.
在dul中，虽然提供了LDR_OUTPUT_IN_UTF8选项，让所有的clob变为UTF8，但是在实际测试中，没有成功.这里对于clob字段处理结果对比：
配置init.dul参数
osd_big_endian_flag=false osd_dba_file_bits=10 osd_c_struct_alignment=32 osd_file_leader_size=1 osd_word_size = 32 feedback = 1000 dc_columns=2000000 dc_tables=10000 dc_objects=1000000 dc_users=400 dc_segments=100000 USE_LOB_FILES =TRUE control_file = control.dul LDR_ENCLOSE_CHAR=| db_block_size=8192 export_mode=FALSE compatible=11
正常情况下测试clob字段
[oracle@xifenfei dul]$ ./dul
Data UnLoader: 10.2.0.5.20 - Internal Only - on Sat Jan 19 00:19:05 2013
with 64-bit io functions
Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL> unload table chf.EVT_T_COMMON_LOG;
. unloading (index organized) table LOB024001aa
DUL: Warning: Recreating file "LOB024001aa.ctl"
0 rows unloaded
. unloading (index organized) table LOB024001da
DUL: Warning: Recreating file "LOB024001da.ctl"
0 rows unloaded
. unloading (index organized) table LOB0240020a
DUL: Warning: Recreating file "LOB0240020a.ctl"
0 rows unloaded
. unloading (index organized) table LOB0240023a
DUL: Warning: Recreating file "LOB0240023a.ctl"
0 rows unloaded
. unloading (index organized) table LOB0240026a
DUL: Warning: Recreating file "LOB0240026a.ctl"
0 rows unloaded
Preparing lob metadata from lob index
Reading LOB024001aa.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB024001da.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240020a.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240023a.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240026a.dat 0 entries loaded and sorted 0 entries
. unloading table EVT_T_COMMON_LOG
DUL: Warning: Recreating file "CHF_EVT_T_COMMON_LOG.ctl"
1863 rows unloaded
--修改CHF_EVT_T_COMMON_LOG.ctl中的导入表名为TEST.T_TEST
--创建测试表
SQL> create table t_test as
2 select * from chf.EVT_T_COMMON_LOG where 1=0;
Table created.
--导入数据
[oracle@xifenfei dul]$ sqlldr test/test control=CHF_EVT_T_COMMON_LOG.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 18 23:50:32 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128
…………
Commit point reached - logical record count 1856
Commit point reached - logical record count 1863
--测试数据是否乱码
SQL> desc chf.EVT_T_COMMON_LOG
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(64)
CASES_OF_STATISTICAL_SITATION CLOB
…………
DEPARTMENT_ID VARCHAR2(40)
MOTIFY_MAN_ID VARCHAR2(40)
SQL> select CASES_OF_STATISTICAL_SITATION from t_test where rownum<3;
CASES_OF_STATISTICAL_SITATION
--------------------------------------------------------------------------------
b*kb
_SfZz
通过试验证明，在dul处理clob字段的时候，很容易出现乱码，因为这里涉及到很多中情况(NLS_LANG,LANG,LDR_OUTPUT_IN_UTF8参数等)，经过了多次试验，均不能成功，这里就是为了给出来一个大概的结论：dul在正常情况下不能完美的处理非英文的clob
修改后lob字段文件属性后测试clob字段
--修改CHF_EVT_T_COMMON_LOG.ctl中的导入表名为TEST.EVT_T_COMMON_LOG --导入数据 [oracle@xifenfei dul]$ sqlldr test/test control=CHF_EVT_T_COMMON_LOG.ctl SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 18 23:50:32 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 64 Commit point reached - logical record count 128 ………… Commit point reached - logical record count 1856 Commit point reached - logical record count 1863 --测试数据是否乱码 SQL> select CASES_OF_STATISTICAL_SITATION from EVT_T_COMMON_LOG where rownum<3; CASES_OF_STATISTICAL_SITATION -------------------------------------------------------------------------------- 1、案件统计情况截止至交班时间Ｃ时间 0 分），今日立难点问题Ｃ7，国庆北路桂门岭社区丽都花园路口，多次上
测试证明:修改了clob文件的相关属性后，完美实现dul处理clob乱码问题