取mysql clob 乱码_完美解决dul处理clob字段乱码问题 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 1342...

使用过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乱码问题