dul 10恢複oracle lob資料方法

dul10恢複oracle lob資料方法  

5.4 LOB欄位的DUL測試 5.4.1 準備一張含BLOB欄位的表 如下示範了如何將LOB檔案從作業系統匯入庫中。 //HR使用者下建一張含BLOB型欄位的表T_BLOB SQL> show userUSER is "HR"SQL> CREATE TABLE T_blob2 (3 image_id number,4 file_name varchar2(30),5 image_data blob6 )7 lob (image_data) store as image_data8 (9 tablespace users10 disable storage in row11 )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 alteredDUL> unload table hr.t_blob;Preparing lob metadata from lob index. unloading (index organized) table LOB00c00223 10 rows unloadedReading LOB00c00223.dat 10 entries loaded and sorted. unloading table T_BLOBDUL: Error: Column IMAGE_DATA of type BLOB cannot be unloaded in export_modeDUL: Warning: Column will be ignored4 rows unloadedDUL> exit 5.4.3 BLOB類型資料實測DUL 9.x版本不支援    匯出模式為sql*loader方式，報錯如下：   DUL> set export_mode=false;Parameter alteredDUL> unload table hr.t_blob;Preparing lob metadata from lob indexReading LOB01800013.dat 18 entries loaded and sorted. unloading table T_BLOBDUL: Error: Block type does not match (expected 27, found 40)DUL: Error: While processing block ts#=6, file#=6, block#=12DUL: Error: Column conversion failed! (type# = 113)00000000 00540001 01080000 00010000 00010000 00001b8b .T.. .... .... .... ....DUL: Error: Col# 2:Column Conversion driver failedDUL: Error: while processing row 0DUL: Error: While processing block ts#=3, file#=3, block#=316DUL: Error: Block type does not match (expected 27, found 40)DUL: Error: While processing block ts#=6, file#=6, block#=39DUL: Error: Column conversion failed! (type# = 113)00000000 00540001 01080000 00010000 00010000 00001b8c .T.. .... .... .... ....DUL: Error: Col# 2:Column Conversion driver failedDUL: Error: while processing row 1DUL: Error: While processing block ts#=3, file#=3, block#=316DUL: Error: Block type does not match (expected 27, found 40)DUL: Error: While processing block ts#=6, file#=6, block#=68DUL: Error: Column conversion failed! (type# = 113)00000000 00540001 01080000 00010000 00010000 00001b8d .T.. .... .... .... ....DUL: Error: Col# 2:Column Conversion driver failedDUL: Error: while processing row 2DUL: Error: While processing block ts#=3, file#=3, block#=316DUL: Error: Block type does not match (expected 27, found 40)DUL: Error: While processing block ts#=6, file#=6, block#=136DUL: Error: Column conversion failed! (type# = 113)00000000 00540001 01080000 00010000 00010000 00001b8e .T.. .... .... .... ....DUL: Error: Col# 2:Column Conversion driver failedDUL: Error: while processing row 3DUL: Error: While processing block ts#=3, file#=3, block#=316DUL: Warning: Recreating file "HR_T_BLOB.ctl"0 rows unloadedDUL> prepare LOB01800013'Dul: parse error: End of statement expected, when parsing <LOB01800013>File "standard input" line number 1 column 8DUL>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 LOB01800013DUL: Warning: Recreating file "LOB01800013.ctl"18 rows unloadedPreparing lob metadata from lob indexReading LOB01800013.dat 18 entries loaded and sorted 18 entries. unloading table T_BLOBDUL: Warning: Recreating file "HR_T_BLOB.ctl"4 rows unloadedDUL>//truncate掉表t_blobSQL> connect hr/hrConnected.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.ctlload datainfile 'HR_T_BLOB.dat'insertinto 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.ctlSQL*Loader: Release 9.2.0.6.0 - Production on Wed Dec 13 08:57:29 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Commit point reached - logical record count 4//查看一下載入結果，可見已成功載入。$ sqlplus hr/hrSQL*Plus: Release 9.2.0.6.0 - Production on Wed Dec 13 08:58:13 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning optionJServer Release 9.2.0.6.0 - ProductionSQL> SELECT image_id,file_name,length(image_data) FROM t_blob;IMAGE_ID FILE_NAME LENGTH(IMAGE_DATA)---------- ---------------------------------------- ------------------1 image01.jpg 1298822 image02.jpg 1958903 image03.jpg 4866964 image04.jpg 124249SQL> 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(*)---------- 16384SQL> 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 ENABLEDSQL>//執行dul,其中bootstrap輸出省略DUL> bootstrap;//dul壓縮表成功DUL> unload table hr.T1;. unloading table T1 16384 rows unloadedDUL>

 

轉載自：http://hi.baidu.com/raid5/blog/item/e84b4ddf98a96f1448540367.html