sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle dul >

【学习笔记】Oracle dul处理long、clob、blob字段有中文的案例

时间:2016-10-30 10:50   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle DUL工具使用的案例,在处理long、clob、blob字段时,字段时含有中文的处理办法。
下面只是用于测试,请误使用到生产环境

1,软件版本

[oracle@oracleplus.net ~]$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
oracleplus.net> 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@oracleplus.net ~]$./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字段有中文字符

oracleplus.net> desc htz.test;
Name Null Type
—————————————– ——– —————————-
ID NUMBER
NAME LONG
oracleplus.net> set lines 200
oracleplus.net> 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@oracleplus.net ~]$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@oracleplus.net ~]$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表,用于导入数据
oracleplus.net> create table htz.test1(id number,name long);

Table created.
[oracle@oracleplus.net ~]$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

oracleplus.net> set lines 200
oracleplus.net> col name for a100
oracleplus.net> select * from htz.test1;

ID NAME
———- —————————————————————————————————-
1 123534
1 东方龙马成都技术工程师:黄廷忠,个人BLOG:www.htz.pw
123 123
这里看到long中文字段是完美支持的。

3,测试BLOB字段有中文字符

oracleplus.net> create table htz.blob (id number,name blob);

Table created.


oracleplus.net> select * from htz.blob;

ID
———-
NAME
——————————————————————————–

B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2
2CB8F6C8CB424C4F473A7777772E68747A2E7077
通过pl/sql可以看到中文字符集
clip_image001[4]
oracleplus.net> 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@oracleplus.net ~]$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@oracleplus.net ~]$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

http://www.oracleplus.netoracleplus.net> select * from htz.blob1;

ID
———-
NAME
——————————————————————————–

B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2
2CB8F6C8CB424C4F473A7777772E68747A2E7077

oracleplus.net> select * from htz.blob;

ID
———-
NAME
——————————————————————————–

B6ABB7BDC1FAC2EDB3C9B6BCB7D6B9ABCBBE4F5241434C45BCBCCAF5B9A4B3CCCAA6BBC6CDA2D6D2
2CB8F6C8CB424C4F473A7777772E68747A2E7077
结果集完全一样。
通过pl/sql查看中文
clip_image002[4]
BLOB中文也完美支持

4 dul测试clob中文字符

oracleplus.net> create table htz.clob (id number,name clob,address clob);

Table created.


oracleplus.net> select * from htz.clob;

ID NAME ADDRESS
———- ———————————————————— —————————————-
123 东方龙马技术工程师:黄廷忠,个人BLOG
123 东方龙马成都分公司ORACLE技术工程师黄廷忠 个人博客
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

oracleplus.net> create table htz.clob1 as select * from htz.clob where 1=2;

Table created.

[oracle@oracleplus.net ~]$mv LF0005.lob LF0005.lob.back
[oracle@oracleplus.net ~]$mv LF0004.lob LF0004.lob.back
[oracle@oracleplus.net ~]$mv LF0003.lob LF0003.lob.back
[oracle@oracleplus.net ~]$mv LF0002.lob LF0002.lob.back
[oracle@oracleplus.net ~]$mv LF0001.lob LF0001.lob.back
[oracle@oracleplus.net ~]$iconv -f UCS-2BE -t gb2312 LF0005.lob.back > LF0005.lob
[oracle@oracleplus.net ~]$iconv -f UCS-2BE -t gb2312 LF0004.lob.back > LF0004.lob
[oracle@oracleplus.net ~]$iconv -f UCS-2BE -t gb2312 LF0003.lob.back > LF0003.lob
[oracle@oracleplus.net ~]$iconv -f UCS-2BE -t gb2312 LF0002.lob.back > LF0002.lob
[oracle@oracleplus.net ~]$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@oracleplus.net ~]$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

oracleplus.net> select * from htz.clob1;

ID NAME ADDRESS
———- ———————————————————— ————————————————–
123 东方龙马技术工程师
123 东方龙马成都分公
124 东方龙马成都分公

本文固定链接: http://www.htz.pw/2014/09/15/dul%e5%a4%84%e7%90%86long%e3%80%81clob%e3%80%81blob%e5%ad%97%e6%ae%b5%e4%b8%ad%e6%9c%89%e4%b8%ad%e6%96%87%e5%86%85%e5%ae%b9.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle dul处理long、clob、blob字段有中文的案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1113.html

Oracle研究中心

关键词:

Oracle dul数据挖掘

Oracle DUL工具使用案例

如何使用Oracle DUL工具处理long、clob、blob类型字段