sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle dul >

【学习笔记】Oracle dul工具从exp文件中直接抽取数据案例

时间:2016-10-25 10:48   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库恢复工具DUL的使用案例,使用Oracle dul工具从exp文件中直接抽取数据的方法。
使用scott这个用户来测试

1,导出scott用户数据

[oracle@oracleplus.net ~]$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@oracleplus.net ~]$./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 cshttp://www.oracleplus.netet 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@oracleplus.net ~]$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@oracleplus.net ~]$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@oracleplus.net ~]$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@oracleplus.net ~]$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@oracleplus.net ~]$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@oracleplus.net ~]$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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle dul工具从exp文件中直接抽取数据案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1062.html

Oracle研究中心

关键词:

oracle dul工具在处理exp文件

Oracle dul工具使用方法

如何从oracle exp文件抽取数据