故障处理：dul直接抽取exp文件
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
故障处理：dul直接抽取exp文件
下面是测试dul工具从exp文件中直接抽取数据，使用scott这个用户来测试
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
1，导出scott用户数据
[oracle@www.htz.pw ~]$exp scott/oracle file=/tmp/exp.dmp
Export: Release 10.2.0.4.0 – Production on Thu Jun 19 01:57:12 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT’s tables via Conventional Path …
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table HTZ 50075 rows exported
. . exporting table HTZ1 100136 rows exported
. . exporting table HTZ2 50069 rows exported
. . exporting table HTZ3 100140 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
2，dul导出某张表
dul在处理exp文件的时候，可以不需要配置任何的参数文件，下面先来看看使用到的两条命令的语法
SCAN DUMP FILE dump file name
[ FROM begin offset ]
[ UNTIL end offset ];
Scans an export dump file to produce to provide the
create/insert statements and the offsets in the dump file.
UNEXP [TABLE] [ owner . ] table name
( column list ) [ DIRECT ]
DUMP FILE dump file name
FROM begin offset [ UNTIL end offset ]
[ MINIMUM minimal number of columns COLUMNS ] ;
To unload data from a corrupted exp dump file. No special setup
or configuration is required, just the compatible parameter.
The start offset should be where a row actually begins.
下面是自己的测试步骤
[oracle@www.htz.pw ~]$./dul1
Data UnLoader 10.2.4.37 – Oracle Internal Only – on Thu Jun 19 01:45:19 2014
with 64-bit io functions
Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal use Only
DUL: Warning: Compatible is set to 10 Values can be 6|7|8|9|10
DUL: Warning: Recreating file "dul.log"
DUL> scan dump file /tmp/exp.dmp
2 ;
0: CSET: 1 (US7ASCII)
3: SEAL EXPORT:V10.02.01
20: DBA SCOTT
28: TYPE USERS
8998: CREATE DATABASE LINK "LINK_TEST" CONNECT TO "SCOTT" IDENTIFIED BY VALUES ’05A788AF637A8C80E8E0D3F5C9434FD3C5′ USING ‘orcl1123’
9255: TABLE "BONUS"
9269: CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER, "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
9519: INSERT INTO "BONUS" ("ENAME", "JOB", "SAL", "COMM") VALUES (:1, :2, :3, :4)
9595: BIND information for 4 columns
col[ 1] type 1 max length 10 cset 852 (ZHS16GBK) form 1
col[ 2] type 1 max length 9 cset 852 (ZHS16GBK) form 1
col[ 3] type 2 max length 22
col[ 4] type 2 max length 22
Conventional export
9625: start of table data
11105: TABLE "DEPT"
11118: CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
11362: INSERT INTO "DEPT" ("DEPTNO", "DNAME", "LOC") VALUES (:1, :2, :3)
11428: BIND information for 3 columns
col[ 1] type 2 max length 22
col[ 2] type 1 max length 14 cset 852 (ZHS16GBK) form 1
col[ 3] type 1 max length 13 cset 852 (ZHS16GBK) form 1
Conventional export
11454: start of table data
11557: CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
11856: ALTER TABLE "DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE
13411: TABLE "EMP"
13423: CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
13765: INSERT INTO "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") VALUES (:1, :2, :3, :4, :5, :6, :7, :8)
13893: BIND information for 8 columns
col[ 1] type 2 max length 22
col[ 2] type 1 max length 10 cset 852 (ZHS16GBK) form 1
col[ 3] type 1 max length 9 cset 852 (ZHS16GBK) form 1
col[ 4] type 2 max length 22
col[ 5] type 12 max length 7
col[ 6] type 2 max length 22
col[ 7] type 2 max length 22
col[ 8] type 2 max length 22
Conventional export
13939: start of table data
14607: CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
14903: ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE
18314: TABLE "HTZ"
18326: CREATE TABLE "HTZ" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6291456 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
18822: INSERT INTO "HTZ" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
19083: BIND information for 13 columns
col[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1
col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 4] type 2 max length 22
col[ 5] type 2 max length 22
col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 7] type 12 max length 7
col[ 8] type 12 max length 7
col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1
col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1
Conventional export
19177: start of table data
5461161: TABLE "HTZ1"
5461174: CREATE TABLE "HTZ1" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 11534336 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
5461672: INSERT INTO "HTZ1" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
5461934: BIND information for 13 columns
col[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1
col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 4] type 2 max length 22
col[ 5] type 2 max length 22
col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 7] type 12 max length 7
col[ 8] type 12 max length 7
col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1
col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1
Conventional export
5462028: start of table data
16320201: TABLE "HTZ2"
16320214: CREATE TABLE "HTZ2" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6291456 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
16320711: INSERT INTO "HTZ2" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
16320973: BIND information for 13 columns
col[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1
col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 4] type 2 max length 22
col[ 5] type 2 max length 22
col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 7] type 12 max length 7
col[ 8] type 12 max length 7
col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1
col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1
Conventional export
16321067: start of table data
21738084: TABLE "HTZ3"
21738097: CREATE TABLE "HTZ3" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 23068672 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
21738595: INSERT INTO "HTZ3" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
21738857: BIND information for 13 columns
col[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1
col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1
col[ 4] type 2 max length 22
col[ 5] type 2 max length 22
col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 7] type 12 max length 7
col[ 8] type 12 max length 7
col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1
col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1
col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1
col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1
Conventional export
21738951: start of table data
32621864: CREATE INDEX "IND_HTZ3_OBJECT_OWNER" ON "HTZ3" ("OBJECT_ID" , "OWNER" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 5242880 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
32628593: TABLE "SALGRADE"
32628610: CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
32628841: INSERT INTO "SALGRADE" ("GRADE", "LOSAL", "HISAL") VALUES (:1, :2, :3)
32628912: BIND information for 3 columns
col[ 1] type 2 max length 22
col[ 2] type 2 max length 22
col[ 3] type 2 max length 22
Conventional export
32628930: start of table data
32630200: ENDTABLE
32630417: TABLE "EMP"
32630429: ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE
32630544: ENDTABLE
32631030: TABLE "BONUS"
32631112: TABLE "DEPT"
32631191: TABLE "EMP"
32631267: TABLE "HTZ1"
32631346: TABLE "HTZ2"
32631425: TABLE "HTZ3"
32631504: TABLE "SALGRADE"
32631595: ENDTABLE
32631652: TABLE "EMP"
32631664: ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"
32631712: ENDTABLE
32631744: EXIT
32631749: EXIT
DUL>
DUL> UNEXP TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) dump file /tmp/exp.dmp from 32628930 until 32630200;
Unloaded 5 rows, end of table marker at 32629007
DUL: Warning: Recreating file "SALGRADE.ctl"
[oracle@www.htz.pw ~]$cat SALGRADE.ctl
load data
infile ‘SALGRADE.dat’
insert
into table "SALGRADE"
fields terminated by whitespace
(
"GRADE" CHAR(1) enclosed by X’7C’
,"LOSAL" CHAR(4) enclosed by X’7C’
,"HISAL" CHAR(4) enclosed by X’7C’
"UNEXP_STATUS" FILLER CHAR(3) enclosed by X’7C’)
)
这一行是需要注意的
[oracle@www.htz.pw ~]$cat SALGRADE.dat
|1| |700| |1200| | |
|2| |1201| |1400| | |
|3| |1401| |2000| | |
|4| |2001| |3000| | |
|5| |3001| |9999| | |
导入数据库中
SQL> truncate table scott.SALGRADE;
Table truncated.
[oracle@www.htz.pw ~]$vi SALGRADE.ctl
"UNEXP_STATUS" FILLER CHAR(3) enclosed by X’7C’)
load data
infile ‘SALGRADE.dat’
insert
into table "SALGRADE"
fields terminated by whitespace
(
"GRADE" CHAR(1) enclosed by X’7C’
,"LOSAL" CHAR(4) enclosed by X’7C’
,"HISAL" CHAR(4) enclosed by X’7C’
)
[oracle@www.htz.pw ~]$sqlldr userid=scott/oracle control=SALGRADE.ctl
SQL*Loader: Release 10.2.0.4.0 – Production on Thu Jun 19 01:54:53 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached – logical record count 5
[oracle@www.htz.pw ~]$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jun 19 01:55:46 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/oracle
Connected.
SQL> select * from SALGRADE;
GRADE LOSAL HISAL
———- ———- ———-
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
下面是sqlldr的日志输出
[oracle@www.htz.pw ~]$cat SALGRADE.log
SQL*Loader: Release 10.2.0.4.0 – Production on Thu Jun 19 01:54:53 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: SALGRADE.ctl
Data File: SALGRADE.dat
Bad File: SALGRADE.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table "SALGRADE", loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
—————————— ———- —– —- —- ———————
"GRADE" FIRST 1 WHT | CHARACTER
"LOSAL" NEXT 4 WHT | CHARACTER
"HISAL" NEXT 4 WHT | CHARACTER
Table "SALGRADE":
5 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 1024 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Thu Jun 19 01:54:53 2014
Run ended on Thu Jun 19 01:54:53 2014
Elapsed time was: 00:00:00.10
CPU time was: 00:00:00.01
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号