在有次8i的库恢复中,因为硬盘损坏导致几个表出现很多诡异性坏块,尝试使用dul对其进行挖掘数据,当时使用dul 9 遇到一个难题:当一张表中有lob类型,同时又有varchar2类型,而且varchar2类型数据中包含回车键,使得解决起来很麻烦(因为export_mode=false支持lob,但是不支持字符串含回车;export_mode=true支持字符串含回车,但是不支持lob),最后放弃了对部分数据的挖掘.这个问题让我一直不甘心,今天测试dul 10 发现是用export_mode=true可以完美解决该问题
创建模拟表和插入数据
SQL> desc t_xff
Name Null? Type
----------------------------------------- -------- ----------------------------
C_BLOB BLOB
C_VARCHAR VARCHAR2(4000)
SQL> declare
2 a_blob BLOB;
3 bfile_name BFILE := BFILENAME('ULTLOBDIR','awr_ora11g_2012-06-01_174_175.html');
4 begin
5 insert into t_xff(C_BLOB,C_VARCHAR) values (
6 empty_blob(),
7 'www.xifenfei.com
8 WWW.XIFENFEI.COM
9 惜分飞
10 欢迎访问惜分飞博客
11 提供数据库异常恢复技术支持')
12 returning C_BLOB into a_blob;
13 dbms_lob.fileopen(bfile_name);
14 dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
15 dbms_lob.fileclose(bfile_name);
16 commit;
17 end;
18 /
PL/SQL procedure successfully completed.
SQL> select length(c_varchar),dbms_lob.getlength(c_blob) from t_xff;
LENGTH(C_VARCHAR) DBMS_LOB.GETLENGTH(C_BLOB)
----------------- --------------------------
61 4282573
SQL> select c_varchar from t_xff;
C_VARCHAR
---------------------------------------------------------------
www.xifenfei.com
WWW.XIFENFEI.COM
惜分飞
欢迎访问惜分飞博客
提供数据库异常恢复技术支持
dul 挖数据
[oracle@xifenfei dul]$ ./dul
Data UnLoader: 10.2.0.5.13 - Internal Only - on Mon Jul 2 04:29:10 2012
with 64-bit io functions
Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL> bootstrap;
DUL> desc chf.t_xff;
Table CHF.T_XFF
obj#= 51353, dataobj#= 51353, ts#= 4, file#= 4, block#=67
tab#= 0, segcols= 2, clucols= 0
Column information:
icol# 01 segcol# 01 C_BLOB len 4000 type 113 BLOB
LOB Segment: dataobj#= 51354, ts#= 4, file#= 4, block#=75 chunk=1
LOB Index: dataobj#= 51355, ts#= 4, file#= 4, block#=83
icol# 02 segcol# 02 C_VARCHAR len 4000 type 1 VARCHAR2 cs 852(ZHS16GBK)
--export_mode=false
DUL> unload table chf.t_xff;
. unloading (index organized) table LOB01000053 65 rows unloaded
Preparing lob metadata from lob index
Reading LOB01000053.dat 65 entries loaded and sorted 65 entries
. unloading table T_XFF 1 row unloaded
--导出数据文件
-rw-r--r-- 1 oracle oinstall 6.1K Jul 2 04:15 LOB01000053.dat
-rw-r--r-- 1 oracle oinstall 335 Jul 2 04:15 LOB01000053.ctl
-rw-r--r-- 1 oracle oinstall 8.2M Jul 2 04:15 CHF_T_XFF.dat
-rw-r--r-- 1 oracle oinstall 263 Jul 2 04:15 CHF_T_XFF.ctl
----export_mode=true
DUL> unload table chf.t_xff;
. unloading (index organized) table LOB01000053
DUL: Warning: Recreating file "LOB01000053.ctl"
65 rows unloaded
Preparing lob metadata from lob index
Reading LOB01000053.dat 65 entries loaded and sorted 65 entries
. unloading table T_XFF 1 row unloaded
--导出数据文件
-rw-r--r-- 1 oracle oinstall 6229 Jul 2 04:29 LOB01000053.dat
-rw-r--r-- 1 oracle oinstall 335 Jul 2 04:29 LOB01000053.ctl
-rw-r--r-- 1 oracle oinstall 4285027 Jul 2 04:29 CHF_T_XFF.dmp
导入数据测试
sqlldr导入
SQL> truncate table chf.t_xff; Table truncated. [oracle@xifenfei dul]$ sqlldr chf/xifenfei control=CHF_T_XFF.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 2 04:23:18 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL*Loader-510: Physical record in data file (CHF_T_XFF.dat) is longer than the maximum(1048576) SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. [oracle@xifenfei dul]$ sqlldr chf/xifenfei control=CHF_T_XFF.ctl readsize=20971520 SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 2 04:26:50 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> select length(c_varchar),dbms_lob.getlength(c_blob) from chf.t_xff; no rows selected --试验结果证明在出现表中同时有lob和varchar2列(含回车)时,export_mode=false不能正常工作
imp导入
SQL> drop table chf.t_xff;
Table dropped.
[oracle@xifenfei dul]$ imp chf/xifenfei file=CHF_T_XFF.dmp full=y
Import: Release 10.2.0.1.0 - Production on Mon Jul 2 04:30:30 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V07.00.07 via conventional path
Warning: the objects were exported by Bernard's DUL, not by you
. importing Bernard's DUL's objects into CHF
. importing Bernard's DUL's objects into CHF
. . importing table "T_XFF" 1 rows imported
SQL> select length(c_varchar),dbms_lob.getlength(c_blob) from t_xff;
LENGTH(C_VARCHAR) DBMS_LOB.GETLENGTH(C_BLOB)
----------------- --------------------------
61 4282573
SQL> select c_varchar from t_xff;
C_VARCHAR
---------------------------------------------------------------
www.xifenfei.com
WWW.XIFENFEI.COM
惜分飞
欢迎访问惜分飞博客
提供数据库异常恢复技术支持
--试验结果证明在出现表中同时有lob和varchar2列(含回车)时,export_mode=true正常工作
加我QQ(107644445)
