Dul 10 oracle lob data restoration method

Dul10 restoration of oracle lob data

5.4 DUL test 5.4.1 of the LOB field prepare a table containing BLOB fields. The following shows how to import the LOB file from the operating system to the database. // CREATE a TABLE 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. // create an SQL * loader control file and prepare four images $ 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 $ // Execute SQL * loader import operation $ 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,200 2, Oracle Corporation. all rights reserved. commit point reached-logical record count 4 $ // verify SQL> S ELECT image_id, file_name, length (image_data) FROM t_blob 2/IMAGE_ID FILE_NAME LENGTH (IMAGE_DATA) ---------- detail ------------------ 1 image01.jpg 129882 2 image02.jpg 195890 3 image03.jpg 486696 4 image04.jpg 124249 SQL> 5.4.2 The Unload mode of a table containing LOB data when the table contains LOB data. when the dmp mode is Unload, data in the LOB column will be lost. In this mode, Unload will report the following warning: 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 data test DUL 9. x does not support SQL * loader in export mode. The following error is returned: 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 faile D! (Type # = 113) 00000000 00540001 01080000 00010000 00010000 running 1b8b. 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 running 1b8c. 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 running 1b8d. 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 running 1b8e. 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 "st Andard input "line number 1 column 8DUL> DUL: Error: Block type does not match (expected 27, found 40) This Error indicates that this version of Dul does not know Blob blocks. 5.4.4 DUL 10. x supports the LOB data test version Dul 10.2.0.21B For solaris. // Unload table hr. t_blob table DUL> unload table hr. t_blob ;. unloading (index organized) table failed: 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 the table t_blobSQL> connect hr/hrConnected. SQL> trun Cate table t_blob; Table truncated. SQL> // view the dul generated SQL * loader script $ ls-l * T_BLOB *-rw-r -- 1 ora2 dba 391 December 13 08:54 HR_T_BLOB.ctl-rw-r -- r -- 1 ora2 dba 124 December 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 fingerprint "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 -- 1 ora2 dba 129882 December 13 08:54 LF0001.lob-rw-r -- 1 ora2 dba 195890 December 13 08:54 LF0002.lob-rw-r -- 1 ora2 dba 486696 08:54 LF0003.lob-rw-r- -R -- 1 ora2 dba 124249 December 13 08:54 LF0004.lob // use SQL * loader to load data to the t_blob table $ sqlldr hr/hr control = HR_T_BLOB.ctlSQL * Loader: release 9.2.0.6.0-Production on Wed Dec 13 08:57:29 2006 Copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. commit point reached-logical record count 4 // check the loading result. It can be seen that the data is successfully loaded. $ Sqlplus hr/hrSQL * Plus: Release 9.2.0.6.0-Production on Wed Dec 13 08:58:13 2006 Copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0-64bit ProductionWith the specified 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 )---------- When ------------------ 1 image01.jpg 1298822 image02.jpg 1958903 image03.jpg 4866964 image04.jpg 123169sql> 5.4.5 dul10.x is used to process LOB data without system tablespace data files. Also, there is a problem in processing LOB data without system tablespace data files, an error is reported during actual measurement, and then an exception exits. DUL> unload table OBJNO6630 (COL001 NUMBER, COL002 VARCHAR2 (30), COL003 BLOB) 2 STORAGE (DATAOBJNO 6630 );. unloading table OBJNO6630 DUL: INTERNAL Error: LOB Chunk size shocould not be zero $5.5 DUL Unload compression table (compress table) test shows that the dul9.x version does not support table compression, when an error is reported or the data output from Unload is only zero, the table is compressed by Dul10.x. Test version Dul 10.2.0.21B For solaris. // A table T1 under HR contains 16384 records, non-compressed SQL> select count (*) from T1; COUNT (*) ---------- 16384SQL> SELECT table_name, compression FROM user_tables WHERE table_name = 't1'; TABLE_NAME COMPRESS ------------------------------ -------- T1 DISABLED // change T1 to a compressed table 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> // execute dul, WHERE the bootstrap output omitting DUL> bootstrap; // The dul table is compressed successfully. DUL> unload table hr. t1 ;. unloading table T1 16384 rows unloadedDUL>

 

Reprinted from: http://hi.baidu.com/raid5/blog/item/e84b4ddf98a96f1448540367.html