当前位置: 首页 > DUL > 正文

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.

2dul导出某张表

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

本文固定链接: http://www.htz.pw/2014/06/21/dul%e7%9b%b4%e6%8e%a5%e6%8a%bd%e5%8f%96exp%e6%96%87%e4%bb%b6.html | 认真就输

该日志由 huangtingzhong 于2014年06月21日发表在 DUL 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: dul直接抽取exp文件 | 认真就输