dul10恢复oracle lob数据方法
|
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> |
转载自：http://hi.baidu.com/raid5/blog/item/e84b4ddf98a96f1448540367.html