5.4 LOB字段的DUL测试
5.4.1 准备一张含BLOB字段的表
如下演示了如何将LOB文件从操作系统导入库中。
//HR用户下建一张含BLOB型字段的表T_BLOB
SQL> show user USER is "HR" SQL> CREATE TABLE T_blob 2 ( 3 image_id number, 4 file_name varchar2(30), 5 image_data blob 6 ) 7 lob (image_data) store as image_data 8 ( 9 tablespace users 10 disable storage in row 11 ) 12 / Table created. //建立sql*loader的控制文件，准备四张图片
$ cat t_blob.ctl
LOAD DATA
INFILE 'images.txt'
INTO TABLE T_BLOB
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
image_id char,
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
$ cat images.txt
1,image01.jpg
2,image02.jpg
3,image03.jpg
4,image04.jpg
$
//执行sql*loader导入操作
$ sqlldr hr/hr control=t_blob.ctl
SQL*Loader: Release 9.2.0.6.0 - Production on Mon Oct 29 14:54:31 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
$
//验证一下
SQL> SELECT image_id,file_name,length(image_data) FROM t_blob
2 /
IMAGE_ID FILE_NAME LENGTH(IMAGE_DATA)
---------- ---------------------------------------- ------------------
1 image01.jpg 129882
2 image02.jpg 195890
3 image03.jpg 486696
4 image04.jpg 124249
SQL>
5.4.2 含LOB型数据的表的Unload模式
当含LOB型数据的表以.dmp方式Unload时， LOB列的数据会丢失。在这样的模式Unload会报如下警告信息：
DUL> set export_mode=true; Parameter altered DUL> unload table hr.t_blob; Preparing lob metadata from lob index . unloading (index organized) table LOB00c00223 10 rows unloaded Reading LOB00c00223.dat 10 entries loaded and sorted . unloading table T_BLOB DUL: Error: Column IMAGE_DATA of type BLOB cannot be unloaded in export_mode DUL: Warning: Column will be ignored 4 rows unloaded DUL> exit
5.4.3 BLOB类型数据实测DUL 9.x版本不支持
导出模式为sql*loader方式，报错如下：
DUL> set export_mode=false; Parameter altered DUL> unload table hr.t_blob; Preparing lob metadata from lob index Reading LOB01800013.dat 18 entries loaded and sorted . unloading table T_BLOB DUL: Error: Block type does not match (expected 27, found 40) DUL: Error: While processing block ts#=6, file#=6, block#=12 DUL: Error: Column conversion failed! (type# = 113) 00000000 00540001 01080000 00010000 00010000 00001b8b .T.. .... .... .... .... DUL: Error: Col# 2:Column Conversion driver failed DUL: Error: while processing row 0 DUL: Error: While processing block ts#=3, file#=3, block#=316 DUL: Error: Block type does not match (expected 27, found 40) DUL: Error: While processing block ts#=6, file#=6, block#=39 DUL: Error: Column conversion failed! (type# = 113) 00000000 00540001 01080000 00010000 00010000 00001b8c .T.. .... .... .... .... DUL: Error: Col# 2:Column Conversion driver failed DUL: Error: while processing row 1 DUL: Error: While processing block ts#=3, file#=3, block#=316 DUL: Error: Block type does not match (expected 27, found 40) DUL: Error: While processing block ts#=6, file#=6, block#=68 DUL: Error: Column conversion failed! (type# = 113) 00000000 00540001 01080000 00010000 00010000 00001b8d .T.. .... .... .... .... DUL: Error: Col# 2:Column Conversion driver failed DUL: Error: while processing row 2 DUL: Error: While processing block ts#=3, file#=3, block#=316 DUL: Error: Block type does not match (expected 27, found 40) DUL: Error: While processing block ts#=6, file#=6, block#=136 DUL: Error: Column conversion failed! (type# = 113) 00000000 00540001 01080000 00010000 00010000 00001b8e .T.. .... .... .... .... DUL: Error: Col# 2:Column Conversion driver failed DUL: Error: while processing row 3 DUL: Error: While processing block ts#=3, file#=3, block#=316 DUL: Warning: Recreating file "HR_T_BLOB.ctl" 0 rows unloaded DUL> prepare LOB01800013' Dul: parse error: End of statement expected, when parsing <LOB01800013> File "standard input" line number 1 column 8 DUL> DUL: Error: Block type does not match (expected 27, found 40)这个报错表明这个版本的Dul不认识Blob块。 5.4.4 DUL 10.x版本支持LOB型数据 测试版本Dul 10.2.0.21B For solaris。 //unload 表hr.t_blob表 DUL> unload table hr.t_blob; . unloading (index organized) table LOB01800013 DUL: Warning: Recreating file "LOB01800013.ctl" 18 rows unloaded Preparing lob metadata from lob index Reading LOB01800013.dat 18 entries loaded and sorted 18 entries . unloading table T_BLOB DUL: Warning: Recreating file "HR_T_BLOB.ctl" 4 rows unloaded DUL> //truncate掉表t_blob SQL> connect hr/hr Connected. SQL> truncate table t_blob; Table truncated. SQL> //查看一下dul生成的sql*loader脚本 $ ls -l *T_BLOB* -rw-r--r-- 1 ora2 dba 391 12月 13 08:54 HR_T_BLOB.ctl -rw-r--r-- 1 ora2 dba 124 12月 13 08:54 HR_T_BLOB.dat $ cat HR_T_BLOB.ctl load data infile 'HR_T_BLOB.dat' insert into table "HR"."T_BLOB" ("IMAGE_ID" CHAR(1) enclosed by X'22' , "FILE_NAME" CHAR(11) enclosed by X'22' , LOBFILE_NAME_COL2 FILLER CHAR(20) enclosed by X'22', "IMAGE_DATA" LOBFILE(LOBFILE_NAME_COL2) TERMINATED BY EOF NULLIF LOBFILE_NAME_COL2 = 'NONE') $ cat HR_T_BLOB.dat "1" "image01.jpg" "LF0001.lob" "2" "image02.jpg" "LF0002.lob" "3" "image03.jpg" "LF0003.lob" "4" "image04.jpg" "LF0004.lob" $ ls -l LF000* -rw-r--r-- 1 ora2 dba 129882 12月 13 08:54 LF0001.lob -rw-r--r-- 1 ora2 dba 195890 12月 13 08:54 LF0002.lob -rw-r--r-- 1 ora2 dba 486696 12月 13 08:54 LF0003.lob -rw-r--r-- 1 ora2 dba 124249 12月 13 08:54 LF0004.lob //用sql*loader把数据载入到表t_blob中 $ sqlldr hr/hr control=HR_T_BLOB.ctl SQL*Loader: Release 9.2.0.6.0 - Production on Wed Dec 13 08:57:29 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Commit point reached - logical record count 4 //查看一下载入结果，可见已成功载入。 $ sqlplus hr/hr SQL*Plus: Release 9.2.0.6.0 - Production on Wed Dec 13 08:58:13 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.6.0 - Production SQL> SELECT image_id,file_name,length(image_data) FROM t_blob; IMAGE_ID FILE_NAME LENGTH(IMAGE_DATA) ---------- ---------------------------------------- ------------------ 1 image01.jpg 129882 2 image02.jpg 195890 3 image03.jpg 486696 4 image04.jpg 124249 SQL>
5.4.5 dul10.x处理无system表空间数据文件的LOB数据还存在问题
dul10.x处理无system表空间数据文件的LOB数据还存在问题，实测时会报错，然后异常退出。
DUL> UNLOAD TABLE OBJNO6630 ( COL001 NUMBER, COL002 VARCHAR2(30), COL003 BLOB)
2 STORAGE( DATAOBJNO 6630 );
. unloading table OBJNO6630
DUL: INTERNAL Error: LOB Chunk size should not be zero
$
5.5 DUL Unload压缩表(compress table)
实测表明，dul9.x的版本不支持压缩表，报错或Unload出来的数据只是零条，而Dul10.x则已经支持了压缩表。测试版本Dul 10.2.0.21B For solaris。
//HR下的一张表T1,含有16384条记录，非压缩 SQL> select count(*) from T1; COUNT(*) ---------- 16384 SQL> SELECT table_name,compression FROM user_tables WHERE table_name='T1'; TABLE_NAME COMPRESS ------------------------------ -------- T1 DISABLED //变更T1为压缩表 SQL> alter table T1 move compress; Table altered. SQL> SELECT table_name,compression FROM user_tables WHERE table_name='T1'; TABLE_NAME COMPRESS ------------------------------ -------- T1 ENABLED SQL> //执行dul,其中bootstrap输出省略 DUL> bootstrap; //dul压缩表成功 DUL> unload table hr.T1; . unloading table T1 16384 rows unloaded DUL>
