我们的文章会在微信公众号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.dmpExport: Release 10.2.0.4.0 – Production on Thu Jun 19 01:57:12 2014Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout 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 SCOTTAbout 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 statisticsExport 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 thecreate/insert statements and the offsets in the dump file.UNEXP [TABLE] [ owner . ] table name( column list ) [ DIRECT ]DUMP FILE dump file nameFROM begin offset [ UNTIL end offset ][ MINIMUM minimal number of columns COLUMNS ] ;To unload data from a corrupted exp dump file. No special setupor configuration is required, just the compatible parameter.The start offset should be where a row actually begins.
下面是自己的测试步骤
[oracle@www.htz.pw ~]$./dul1Data UnLoader 10.2.4.37 – Oracle Internal Only – on Thu Jun 19 01:45:19 2014with 64-bit io functionsCopyright (c) 1994 2010 Bernard van Duijnen All rights reserved.Strictly Oracle Internal use OnlyDUL: Warning: Compatible is set to 10 Values can be 6|7|8|9|10DUL: Warning: Recreating file "dul.log"DUL> scan dump file /tmp/exp.dmp2 ;0: CSET: 1 (US7ASCII)3: SEAL EXPORT:V10.02.0120: DBA SCOTT28: TYPE USERS8998: 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 NOCOMPRESS9519: INSERT INTO "BONUS" ("ENAME", "JOB", "SAL", "COMM") VALUES (:1, :2, :3, :4)9595: BIND information for 4 columnscol[ 1] type 1 max length 10 cset 852 (ZHS16GBK) form 1col[ 2] type 1 max length 9 cset 852 (ZHS16GBK) form 1col[ 3] type 2 max length 22col[ 4] type 2 max length 22Conventional export9625: start of table data11105: 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 NOCOMPRESS11362: INSERT INTO "DEPT" ("DEPTNO", "DNAME", "LOC") VALUES (:1, :2, :3)11428: BIND information for 3 columnscol[ 1] type 2 max length 22col[ 2] type 1 max length 14 cset 852 (ZHS16GBK) form 1col[ 3] type 1 max length 13 cset 852 (ZHS16GBK) form 1Conventional export11454: start of table data11557: 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" LOGGING11856: 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 ENABLE13411: 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 NOCOMPRESS13765: 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 columnscol[ 1] type 2 max length 22col[ 2] type 1 max length 10 cset 852 (ZHS16GBK) form 1col[ 3] type 1 max length 9 cset 852 (ZHS16GBK) form 1col[ 4] type 2 max length 22col[ 5] type 12 max length 7col[ 6] type 2 max length 22col[ 7] type 2 max length 22col[ 8] type 2 max length 22Conventional export13939: start of table data14607: 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" LOGGING14903: 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 ENABLE18314: 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 NOCOMPRESS18822: 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 columnscol[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 4] type 2 max length 22col[ 5] type 2 max length 22col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 7] type 12 max length 7col[ 8] type 12 max length 7col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1Conventional export19177: start of table data5461161: 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 NOCOMPRESS5461672: 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 columnscol[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 4] type 2 max length 22col[ 5] type 2 max length 22col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 7] type 12 max length 7col[ 8] type 12 max length 7col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1Conventional export5462028: start of table data16320201: 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 NOCOMPRESS16320711: 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 columnscol[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 4] type 2 max length 22col[ 5] type 2 max length 22col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 7] type 12 max length 7col[ 8] type 12 max length 7col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1Conventional export16321067: start of table data21738084: 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 NOCOMPRESS21738595: 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 columnscol[ 1] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 2] type 1 max length 128 cset 852 (ZHS16GBK) form 1col[ 3] type 1 max length 30 cset 852 (ZHS16GBK) form 1col[ 4] type 2 max length 22col[ 5] type 2 max length 22col[ 6] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 7] type 12 max length 7col[ 8] type 12 max length 7col[ 9] type 1 max length 19 cset 852 (ZHS16GBK) form 1col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1Conventional export21738951: start of table data32621864: 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" LOGGING32628593: 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 NOCOMPRESS32628841: INSERT INTO "SALGRADE" ("GRADE", "LOSAL", "HISAL") VALUES (:1, :2, :3)32628912: BIND information for 3 columnscol[ 1] type 2 max length 22col[ 2] type 2 max length 22col[ 3] type 2 max length 22Conventional export32628930: start of table data32630200: ENDTABLE32630417: TABLE "EMP"32630429: ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE32630544: ENDTABLE32631030: TABLE "BONUS"32631112: TABLE "DEPT"32631191: TABLE "EMP"32631267: TABLE "HTZ1"32631346: TABLE "HTZ2"32631425: TABLE "HTZ3"32631504: TABLE "SALGRADE"32631595: ENDTABLE32631652: TABLE "EMP"32631664: ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"32631712: ENDTABLE32631744: EXIT32631749: EXITDUL>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 32629007DUL: Warning: Recreating file "SALGRADE.ctl"[oracle@www.htz.pw ~]$cat SALGRADE.ctlload datainfile ‘SALGRADE.dat’insertinto 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 datainfile ‘SALGRADE.dat’insertinto 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.ctlSQL*Loader: Release 10.2.0.4.0 – Production on Thu Jun 19 01:54:53 2014Copyright (c) 1982, 2007, Oracle. All rights reserved.Commit point reached – logical record count 5[oracle@www.htz.pw ~]$sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 – Production on Thu Jun 19 01:55:46 2014Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> conn scott/oracleConnected.SQL> select * from SALGRADE;GRADE LOSAL HISAL———- ———- ———-1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999
下面是sqlldr的日志输出
[oracle@www.htz.pw ~]$cat SALGRADE.logSQL*Loader: Release 10.2.0.4.0 – Production on Thu Jun 19 01:54:53 2014Copyright (c) 1982, 2007, Oracle. All rights reserved.Control File: SALGRADE.ctlData File: SALGRADE.datBad File: SALGRADE.badDiscard File: none specified(Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array: 64 rows, maximum of 256000 bytesContinuation: none specifiedPath used: ConventionalTable "SALGRADE", loaded from every logical record.Insert option in effect for this table: INSERTColumn 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: 1048576Total logical records skipped: 0Total logical records read: 5Total logical records rejected: 0Total logical records discarded: 0Run began on Thu Jun 19 01:54:53 2014Run ended on Thu Jun 19 01:54:53 2014Elapsed time was: 00:00:00.10CPU 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)
