下面是测试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 Scans an export dump file to produce to provide the UNEXP [TABLE] [ owner . ] table name To unload data from a corrupted exp dump file. No special setup |
下面是自己的测试步骤
|
[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 |
dul直接抽取exp文件：等您坐沙发呢！