故障分析：dul处理long、clob、blob字段中有中文内容
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
dul处理long、clob、blob字段中有中文内容
下面只是用于测试，请误使用到生产环境
1，软件版本
[oracle@www.htz.pw ~]$lsb_release -a
LSB Version: :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: RedHatEnterpriseAS
Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Release: 4
Codename: NahantUpdate8
www.htz.pw > select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
[oracle@www.htz.pw ~]$./dul1
Data UnLoader 10.2.4.37 – Oracle Internal Only – on Mon Sep 15 22:40:52 2014
with 64-bit io functions
Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal use Only
2，测试long字段有中文字符
www.htz.pw > desc htz.test;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER
NAME LONG
www.htz.pw > set lines 200
www.htz.pw > select * from htz.test;
ID NAME
———- ——————————————————————————–
1 123534
1 东方龙马成都技术工程师:黄廷忠，个人BLOG:www.htz.pw
123 123
DUL> unload table htz.test;
. unloading table TEST
DUL: Warning: Recreating file "HTZ_TEST.ctl"
3 rows unloaded
[oracle@www.htz.pw ~]$cat HTZ_TEST.ctl
load data
CHARACTERSET ZHS16GBK
infile ‘HTZ_TEST.dat’
insert
into table "HTZ"."TEST1"这里将原来的TEST更改为TEST1
fields terminated by whitespace
(
"ID" CHAR(3) enclosed by X’7C’
,"NAME" CHAR(30) enclosed by X’7C’
)
[oracle@www.htz.pw ~]$od -x HTZ_TEST.dat
0000000 317c 207c 317c 3332 3335 7c34 7c0a 7c31
0000020 7c20 abb6 bdb7 fac1 edc2 c9b3 bcb6 bcbc
0000040 f5ca a4b9 ccb3 a6ca bb3a cdc6 d6a2 a3d2
0000060 b8ac c8f6 42cb 4f4c 3a47 7777 2e77 7468
0000100 2e7a 7770 0a7c 317c 3332 207c 317c 3332
0000120 0a7c
0000122
创建一个test1表，用于导入数据
www.htz.pw > create table htz.test1(id number,name long);
Table created.
[oracle@www.htz.pw ~]$sqlldr htz/oracle control=HTZ_TEST.ctl
SQL*Loader: Release 11.2.0.3.0 – Production on Fri Sep 12 08:23:51 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached – logical record count 3
www.htz.pw > set lines 200
www.htz.pw > col name for a100
www.htz.pw > select * from htz.test1;
ID NAME
———- —————————————————————————————————-
1 123534
1 东方龙马成都技术工程师:黄廷忠，个人BLOG:www.htz.pw
123 123
这里看到long中文字段是完美支持的。
3，测试BLOB字段有中文字符
www.htz.pw > create table htz.blob (id number,name blob);
Table created.
www.htz.pw > select * from htz.blob;
ID
———-
NAME
——————————————————————————–
B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2
2CB8F6C8CB424C4F473A7777772E68747A2E7077
通过pl/sql可以看到中文字符集
clip_image001[4]
www.htz.pw > create table htz.blob1 as select * from htz.blob where 1=2;
Table created.
DUL> desc htz.blob;
Table HTZ.BLOB
obj#= 81834, dataobj#= 81834, ts#= 4, file#= 4, block#=9370
tab#= 0, segcols= 2, clucols= 0
Column information:
icol# 01 segcol# 01 ID len 22 type 2 NUMBER(0,-127)
icol# 02 segcol# 02 NAME len 4000 type 113 BLOB
LOB Segment: dataobj#= 81835, ts#= 4, file#= 4, block#=9378 chunk=1
LOB Index: dataobj#= 81836, ts#= 4, file#= 4, block#=9386
DUL> unload table htz.blob;
. unloading (index organized) table LOB010024aa 0 rows unloaded
Preparing lob metadata from lob index
Reading LOB010024aa.dat 0 entries loaded and sorted 0 entries
. unloading table BLOB 1 row unloaded
-rw-r–r– 1 oracle oinstall 0 Sep 12 09:33 LOB010024aa.dat
-rw-r–r– 1 oracle oinstall 335 Sep 12 09:33 LOB010024aa.ctl
-rw-r–r– 1 oracle oinstall 60 Sep 12 09:33 LF0001.lob
-rw-r–r– 1 oracle oinstall 16 Sep 12 09:33 HTZ_BLOB.dat
-rw-r–r– 1 oracle oinstall 330 Sep 12 09:33 HTZ_BLOB.ctl
[oracle@www.htz.pw ~]$od -x LF0001.lob
0000000 abb6 bdb7 fac1 edc2 c9b3 bcb6 d6b7 abb9
0000020 becb 524f 4341 454c bcbc f5ca a4b9 ccb3
0000040 a6ca c6bb a2cd d2d6 b82c c8f6 42cb 4f4c
0000060 3a47 7777 2e77 7468 2e7a 7770
0000074
[oracle@www.htz.pw ~]$sqlldr htz/oracle control=HTZ_BLOB.ctl
SQL*Loader: Release 11.2.0.3.0 – Production on Fri Sep 12 09:47:25 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached – logical record count 1
www.htz.pw > select * from htz.blob1;
ID
———-
NAME
——————————————————————————–
B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2
2CB8F6C8CB424C4F473A7777772E68747A2E7077
www.htz.pw > select * from htz.blob;
ID
———-
NAME
——————————————————————————–
B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2
2CB8F6C8CB424C4F473A7777772E68747A2E7077
结果集完全一样。
通过pl/sql查看中文
clip_image002[4]
BLOB中文也完美支持
4，dul测试clob中文字符
www.htz.pw > create table htz.clob (id number,name clob,address clob);
Table created.
www.htz.pw > select * from htz.clob;
ID NAME ADDRESS
———- ———————————————————— —————————————-
123 东方龙马技术工程师：黄廷忠，个人BLOG：www.htz.pw www.htz.pw
123 东方龙马成都分公司ORACLE技术工程师黄廷忠 个人博客www.htz.pw
124 东方龙马成都分公司ORACLE技术工程师黄廷忠
DUL> desc htz.clob;
Table HTZ.CLOB
obj#= 81807, dataobj#= 81807, ts#= 4, file#= 4, block#=10186
tab#= 0, segcols= 3, clucols= 0
Column information:
icol# 01 segcol# 01 ID len 22 type 2 NUMBER(0,-127)
icol# 02 segcol# 02 NAME len 4000 type 112 CLOB cs 852(ZHS16GBK)
LOB Segment: dataobj#= 81808, ts#= 4, file#= 4, block#=10194 chunk=1
LOB Index: dataobj#= 81809, ts#= 4, file#= 4, block#=10202
icol# 03 segcol# 03 ADDRESS len 4000 type 112 CLOB cs 852(ZHS16GBK)
LOB Segment: dataobj#= 81810, ts#= 4, file#= 4, block#=10210 chunk=1
LOB Index: dataobj#= 81811, ts#= 4, file#= 4, block#=10218
DUL> unload table htz.clob;
. unloading (index organized) table LOB010027da 0 rows unloaded
. unloading (index organized) table LOB010027ea 0 rows unloaded
Preparing lob metadata from lob index
Reading LOB010027da.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB010027ea.dat 0 entries loaded and sorted 0 entries
. unloading table CLOB
DUL: Warning: Recreating file "HTZ_CLOB.ctl"
3 rows unloaded
www.htz.pw > create table htz.clob1 as select * from htz.clob where 1=2;
Table created.
[oracle@www.htz.pw ~]$mv LF0005.lob LF0005.lob.back
[oracle@www.htz.pw ~]$mv LF0004.lob LF0004.lob.back
[oracle@www.htz.pw ~]$mv LF0003.lob LF0003.lob.back
[oracle@www.htz.pw ~]$mv LF0002.lob LF0002.lob.back
[oracle@www.htz.pw ~]$mv LF0001.lob LF0001.lob.back
[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0005.lob.back > LF0005.lob
[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0004.lob.back > LF0004.lob
[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0003.lob.back > LF0003.lob
[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0002.lob.back > LF0002.lob
[oracle@www.htz.pw ~]$iconv -f UCS-2BE -t gb2312 LF0001.lob.back > LF0001.lob
其实这里我们可以通过下面这条命令一步搞定
ls -l LF*.lob|grep -v grep|awk ‘{cmd="mv "$9" "$9".back";print(cmd);system(cmd);cmd1="iconv -f UCS-2BE -t gb2312 "$9".back > "$9;print(cmd1);system(cmd1);cmd3="rm "$9".back";print(cmd3);system(cmd3)}’
[oracle@www.htz.pw ~]$sqlldr htz/oracle control=HTZ_CLOB.ctl
SQL*Loader: Release 11.2.0.3.0 – Production on Thu Sep 11 23:54:08 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached – logical record count 3
www.htz.pw > select * from htz.clob1;
ID NAME ADDRESS
———- ———————————————————— ————————————————–
123 东方龙马技术工程师：黄廷忠，个人BLOG：www.htz.pw www.htz.pw
123 东方龙马成都分公司ORACLE技术工程师黄廷忠 个人博客www.htz.pw
124 东方龙马成都分公司ORACLE技术工程师黄廷忠
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号