使用过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
|
测试证明:修改了clob文件的相关属性后，完美实现dul处理clob乱码问题
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-11-24 17:56:38 |
赞(
操作
