Life is DUL without it:DUL流程
【IT168技术文档】
上一篇：http://publish.itpub.net/db/2007-11-13/200711130951031.shtml
4 DUL流程
4.1 有系统表空间数据文件时
此种情况下，操作比较容易，流程如下：
○1 依不同的系统环境和数据库环境创建init.dul和control.dul文件。
上面已经详细的说明了生成方法。
○2 启动dul，转载数据字典。
$dul
DUL>bootstrap;
○3 转载你要转载的对象，例如库、用户或某一个表
DUL>unload database;
DUL>unload user hr;
DUL>unload table hr.employees;
4.2 无系统表空间数据文件时
这种情况下，要求你非常了解你的应用和你应用的表，此时如果你不知转载出来的数据源于哪张表，那么这些数据将没有任何用处，有如下几点麻烦需要你处理：
DUL能够猜出表的大部列的数据类型，但是表名及列名都丢失了，而且DUL猜出的列名也有可能是错的。
如果表中的一列是TRAILING NULL的，这样的列DUL是发现不了的，因为这样的列在数据库内并不存储。
被TRUNCATE/DROP掉的表可能会被找到，如果相应的空间未被重用的话。因为对于一个表的TRUNCATE/DROP操作，并不实际的去删除数据，只是把要删除的表从数据字典移除而已。
空表(没有数据)将被遗漏。
这种情况，操作流程如下：
○1 依不同的系统环境和数据库环境创建init.dul和control.dul文件。
○2 启动DUL，扫描数据库生成成SEG.dat和EXT.dat文件。
DUL>scan database;
○3 告诉DUL用scan database生成的SEG.dat和EXT.dat做为输入文件，并以些扫描表段及EXTENT信息。
DUL>alter session set use_scanned_extent_map=TRUE;
DUL>scan tables;
或
DUL>scan extents;
○4 凭借你对应用的了解，辨识出scan tables或scan extents生成的结果，需要注意的是这是一个非常艰苦的工作，且需要充足的耐心。
○5 转载出你认识的表，例如，可以用如下的命令：
DUL> unload table departments
2 (department_id number
3 ,department_name varchar2(30)
4 ,manager_id number
5 ,location_id number)
6 storage (objno 31444);
. unloading table DEPARTMENTS 27 rows unloaded
5 DUL 使用示例
5.1 无系统表空间的数据转载
如下以无系统表空间这种状况下恢复hr下的employees这张表示例一下这种状况的操作流程。
5.1.1 模拟问题
○1去掉control.dul中的系统表空间的数据文件，删除dul工作目录所有的.ctl及,dat文件。
$ cat control.dul 1 2 /yang/oradata/orcl/undotbs01.dbf 3 3 /yang/oradata/orcl/example01.dbf 4 4 /yang/oradata/orcl/indx01.dbf 5 5 /yang/oradata/orcl/tools01.dbf 6 6 /yang/oradata/orcl/users01.dbf 6 7 /yang/oradata/orcl/users02.dbf ○2启动dul验证一下，bootstrap会报错。 $ dul Data UnLoader 9.2.4.4.2 - Internal Use Only - on Mon Oct 29 08:57:38 2007 with 64-bit io functions Copyright (c) 1994 2004 Bernard van Duijnen All rights reserved. DUL> bootstrap; Scanning SYSTEM tablespace to locate compatibility segment ... DUL: Warning: No files found for tablespace 0 Reading EXT.dat 0 entries loaded and sorted Reading SEG.dat 0 entries loaded Reading COMPATSEG.dat 0 entries loaded DUL: Error: No compatibility segments found DUL> 5.1.2 描扫库生成EXT.dat和SEG.dat文件 DUL> scan database; DUL: Warning: Recreating file "EXT.dat" DUL: Warning: Recreating file "SEG.dat" DUL: Warning: Recreating file "COMPATSEG.dat" tablespace 1, data file 2: 25599 blocks scanned tablespace 3, data file 3: 15359 blocks scanned tablespace 4, data file 4: 3199 blocks scanned tablespace 5, data file 5: 1279 blocks scanned tablespace 6, data file 6: 3199 blocks scanned tablespace 6, data file 7: 639 blocks scanned Reading EXT.dat 80 entries loaded and sorted Reading SEG.dat 68 entries loaded Reading COMPATSEG.dat 0 entries loaded 5.1.3 用scan database的结果扫描数据文件中的表。 DUL> alter session set use_scanned_extent_map = true; Parameter altered DUL> scan tables;
Scan tables会生成大量的输出，可以用crt软件的log session功能等方式所这些输出保存下来，接下来的工作就需要很大的细心、耐心及对应用十分的了解。Scan tables的结果中，dul会猜各个表各个字段的类型，并给出几条示例的记录，同时也给出来unload的语句。如下以hr.employees为例，从输出结果中找到如下一段。
object信息
Analyzing segment: data object id=6302 segment header at ( file=3 block=83)
heap organized table
列信息，可见dul找到的这个object有11列，与hr.employees一样
Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice 1 107 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 2 107 11 0 100 100 0 0 0 0 0 0 0 0 0 0 26 0 0 3 107 11 0 100 100 0 0 0 0 0 0 0 0 0 0 35 0 0 4 107 8 0 100 100 0 0 0 0 0 0 0 0 0 0 53 0 0 5 107 18 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 6 107 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0 7 107 10 0 100 100 0 0 0 0 0 0 0 0 0 0 91 0 0 8 107 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 9 107 2 67 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 10 107 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 11 106 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 "198" "Donald" "OConnell" "DOCONNEL" "650.507.9833" "21-JUN-1999 AD 00:00:00" "SH_CLERK" "2600" "" "124" "50" "199" "Douglas" "Grant" "DGRANT" "650.507.9844" "13-JAN-2000 AD 00:00:00" "SH_CLERK" "2600" "" "124" "50" "200" "Jennifer" "Whalen" "JWHALEN" "515.123.4444" "17-SEP-1987 AD 00:00:00" "AD_ASST" "4400" "" "101" "10" "201" "Michael" "Hartstein" "MHARTSTE" "515.123.5555" "17-FEB-1996 AD 00:00:00" "MK_MAN" "13000" "" "100" "20" "202" "Pat" "Fay" "PFAY" "603.123.6666" "17-AUG-1997 AD 00:00:00" "MK_REP" "6000" "" "201" "20" dul 给出的unload语句，从中我们可以进一步确认这个表是不是hr用户下的employees表，dul并给出了所猜的各个列的类型。 UNLOAD TABLE OBJNO6302 ( COL001 NUMBER, COL002 VARCHAR2(11), COL003 VARCHAR2(11) , COL004 VARCHAR2(8), COL005 VARCHAR2(18), COL006 DATE, COL007 VARCHAR2(10) , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER ) STORAGE( DATAOBJNO 6302 ); 5.1.4 Unload 表 ○1指定以.dmp方式导出，当然可以选sql*loader方式。 DUL> set export_mode=TRUE; Parameter altered ○2 对5.1.3中dul生成的Unload语句修改一下表名，并执行这条语句完成Unload操作。 DUL> UNLOAD TABLE EMPLOYEES ( COL001 NUMBER, COL002 VARCHAR2(11), COL003 VARCHAR2(11) 2 , COL004 VARCHAR2(8), COL005 VARCHAR2(18), COL006 DATE, COL007 VARCHAR2(10) 3 , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER ) 4 STORAGE( DATAOBJNO 6302 ); . unloading table EMPLOYEES 107 rows unloaded DUL> exit Life is DUL without it ○3查看一下结果。 $ ls -l EMPLOYEE* -rw-r--r-- 1 ora2 dba 10120 10月 29 09:29 EMPLOYEES.dmp
5.2 被DROP/TRUNCATE表的恢复
用Dul对drop/truncate的表的恢复的操作流程与5.1是一致的，不同的是这一部份找寻的数据量相对于5.1要小的很多，且这部分有一些限制。前而已经提到，Oracle Drop/truncate一张表的操作从10046的跟踪上可以看到只是对数据字典的操作，并不操作实际的数据块。Dul恢复被drop/truncate了的表成功与否取决于被drop/truncate了的表所占用的数据块是不是被新的段(表、索引等)所重用，如果被重用就无法完成这样的恢复了。如下以对被drop了的表的恢复给个例子：
5.2.1 在hr下建一张表T1,插入一些数据
SQL> desc T1
5.2.3 启动dul,scan database，如果你知道这个表可能在哪些数据文件中，可以在control.dul中去掉其它的文件以减少scan的量。
$ dul Data UnLoader 9.2.4.4.2 - Internal Use Only - on Mon Oct 29 10:07:43 2007 with 64-bit io functions Copyright (c) 1994 2004 Bernard van Duijnen All rights reserved. DUL> scan database; DUL: Warning: Recreating file "EXT.dat" DUL: Warning: Recreating file "SEG.dat" DUL: Warning: Recreating file "COMPATSEG.dat" tablespace 1, data file 2: 25599 blocks scanned tablespace 3, data file 3: 15359 blocks scanned tablespace 4, data file 4: 3199 blocks scanned tablespace 5, data file 5: 1279 blocks scanned tablespace 6, data file 6: 3199 blocks scanned tablespace 6, data file 7: 639 blocks scanned Reading EXT.dat 81 entries loaded and sorted Reading SEG.dat 68 entries loaded Reading COMPATSEG.dat 0 entries loaded
5.2.4 扫描表，从中找出删除的表
5.2.5 修改unload语句，完成Unload操作
DUL> UNLOAD TABLE T1 ( ID NUMBER, NAME VARCHAR2(12) )
2 STORAGE( DATAOBJNO 6579 );
. unloading table T1 16384 rows unloaded
DUL> exit
Life is DUL without it
5.3 offline drop 数据文件中表的恢复
这是在实际操作中特别常见的，经常有一些用户的数据库处于非归档的状态，且数据库没有良好的备份。由于操作系统的异常或突然的断电导致了一些数据文件头的损坏，此时启动数据库会报这些数据文件需要介质恢复，因没有归档方式的备份很多的用户为尽快的将数据库启动，便对这些数据文件执行了offfline drop 操作，然后open数据库。Dul是非常适用这种情况下数据库恢复的。如下以offline drop掉usres表空间的数据文件’ /yang/oradata/orcl/users02.dbf’为例来说明一下这种方式的恢复问题。
5.3.1 模拟问题的产生
○1 USERS表空间有两个数据文件，users02.dbf所对应的file#为7
SQL> select tablespace_name,file_name 2 from dba_data_files where tablespace_name='USERS'; TABLESPACE_NAME FILE_NAME ------------------------------ ---------------------------------------- USERS /yang/oradata/orcl/users01.dbf USERS /yang/oradata/orcl/users02.dbf SQL> SQL> select file#,name from v$datafile 2 where name='/yang/oradata/orcl/users02.dbf'; FILE# NAME ---------- ---------------------------------------- 7 /yang/oradata/orcl/users02.dbf SQL> ○2 offline drop掉file#=7的数据文件 SQL> alter database datafile 7 offline drop; Database altered. SQL>
○3 查一下file#=7中都含有哪些段,如下查询可以看出它含有分区表RANGE_TAB_EXAMPLE的一个分区
SQL> SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, RELATIVE_FNO
2 FROM SYS.SYS_DBA_SEGS W
3 WHERE RELATIVE_FNO = 7
4 /
OWNER SEGMENT_NAME PARTITION_NAME RELATIVE_FNO
----- ------------------ -------------------- ------------
HR RANGE_TAB_EXAMPLE PART2 7
○4 查一下offline drop掉的文件中的分区会报错
SQL>
SQL> select count(*) from hr.RANGE_TAB_EXAMPLE partition(part2);
select count(*) from hr.RANGE_TAB_EXAMPLE partition(part2)
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/yang/oradata/orcl/users02.dbf'
SQL>
5.3.2 Dul恢复
○1 把file#=7添加到control.dul文件中
$ cat control.dul
0 1 /yang/oradata/orcl/system01.dbf
1 2 /yang/oradata/orcl/undotbs01.dbf
3 3 /yang/oradata/orcl/example01.dbf
4 4 /yang/oradata/orcl/indx01.dbf
5 5 /yang/oradata/orcl/tools01.dbf
6 6 /yang/oradata/orcl/users01.dbf
6 7 /yang/oradata/orcl/users02.dbf
$
○2 启动dul，执行bootstrap.
$ dul
Data UnLoader 9.2.4.4.2 - Internal Use Only - on Mon Oct 29 13:00:31 2007
with 64-bit io functions
Copyright (c) 1994 2004 Bernard van Duijnen All rights reserved.
DUL: Warning: Recreating file "dul.log"
DUL> bootstrap;
Scanning SYSTEM tablespace to locate compatibility segment ...
tablespace 0, data file 1: 31999 blocks scanned
Reading EXT.dat 1052 entries loaded and sorted
Reading SEG.dat 986 entries loaded
Reading COMPATSEG.dat 1 entries loaded
Compatibility segment found at file = 1, block = 417
database version 9 bootstrap$ at file 1, block 377
. unloading table BOOTSTRAP$ 57 rows unloaded
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 9
OBJ$: segobjno 18, file 1
TAB$: segobjno 2, tabno 1, file 1
COL$: segobjno 2, tabno 5, file 1
USER$: segobjno 10, tabno 1, file 1
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$ 6447 rows unloaded
. unloading table TAB$ 531 rows unloaded
. unloading table COL$ 27905 rows unloaded
. unloading table USER$ 29 rows unloaded
Reading USER.dat 29 entries loaded
Reading OBJ.dat 6447 entries loaded
Reading TAB.dat 531 entries loaded
Reading COL.dat 27905 entries loaded
Reading SEG.dat 986 entries loaded
Reading EXT.dat 1052 entries loaded and sorted
Reading COMPATSEG.dat 1 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 9
OBJ$: segobjno 18, file 1
TAB$: segobjno 2, tabno 1, file 1
COL$: segobjno 2, tabno 5, file 1
USER$: segobjno 10, tabno 1, file 1
TABPART$: segobjno 230, file 1
INDPART$: segobjno 234, file 1
TABCOMPART$: segobjno 249, file 1
INDCOMPART$: segobjno 253, file 1
TABSUBPART$: segobjno 240, file 1
INDSUBPART$: segobjno 245, file 1
IND$: segobjno 2, tabno 3, file 1
ICOL$: segobjno 2, tabno 4, file 1
LOB$: segobjno 2, tabno 6, file 1
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
6447 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
531 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
27905 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
29 rows unloaded
. unloading table TABPART$ 29 rows unloaded
. unloading table INDPART$ 24 rows unloaded
. unloading table TABCOMPART$ 0 rows unloaded
. unloading table INDCOMPART$ 0 rows unloaded
. unloading table TABSUBPART$ 0 rows unloaded
. unloading table INDSUBPART$ 0 rows unloaded
. unloading table IND$ 584 rows unloaded
. unloading table ICOL$ 1067 rows unloaded
. unloading table LOB$ 51 rows unloaded
Reading USER.dat 29 entries loaded
Reading OBJ.dat 6447 entries loaded
Reading TAB.dat 531 entries loaded
Reading COL.dat 27905 entries loaded
Reading SEG.dat 986 entries loaded
Reading EXT.dat 1052 entries loaded and sorted
Reading TABPART.dat 29 entries loaded and sorted
Reading TABCOMPART.dat 0 entries loaded and sorted
Reading TABSUBPART.dat 0 entries loaded and sorted
Reading INDPART.dat 24 entries loaded and sorted
Reading INDCOMPART.dat 0 entries loaded and sorted
Reading INDSUBPART.dat 0 entries loaded and sorted
Reading IND.dat 584 entries loaded
Reading LOB.dat 51 entries loaded
Reading ICOL.dat 1067 entries loaded
Reading COMPATSEG.dat 1 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
○3 设定以.dmp为导出模式，并完成hr.RANGE_TAB_EXAMPLE表的导出。
DUL> set export_mode=true;
Parameter altered
DUL> unload table hr.RANGE_TAB_EXAMPLE;
. unloading table RANGE_TAB_EXAMPLE
. Unloading partition PART1
. Unloading partition PART2
. table RANGE_TAB_EXAMPLE total 5 rows unloaded
DUL>
○4 可见PART2分区unload成功，把RANGE_TAB_EXAMPLE重建掉，导入Dul Unload出来的数据到相应的表即可。
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>
