手动生成字典文件使用dul工具unload数据的全过程

实验环境： OS winxp sp2

DB Oracle816

SID TEST

DUL: DUL8

一、创建测试用户和测试对象

SQL> create user zlhis identified by his default tablespace users ;

用户已创建

SQL> grant dba to zlhis;

授权成功。

SQL> connect zlhis/his;

已连接。

SQL> create table test_a tablespace users as select * from dba_objects;

表已创建。

SQL> create table test_b tablespace users as select * from dba_objects;

表已创建。

SQL> create table test_c tablespace users as select * from dba_objects;

表已创建。

二、根据数据库的参数文件调整init.dul文件中的参数，主要注意db_block_size要与数据库的参数一致。control_file是指dul工具的当前路径下的control文件命名：

内容如：

osd_big_endian_flag=false

osd_dba_file_bits=10

osd_c_struct_alignment=32

osd_file_leader_size=1

osd_word_size = 32

dc_columns=2000000

dc_tables=10000

dc_objects=1000000

dc_users=400

dc_segments=100000

control_file = control.txt

db_block_size=8192

export_mode=true

compatible=8

file = dump

三、生成 control.txt文件(文件由control_file参数指定)

可以用select ts#, rfile#, name from v$datafile语句生成control.txt文件内容,文件内容为要挖掘数据的数据库数据文件,格式为：

ts#, rfile#, 数据文件名

如：

select ts#, rfile#, name from v$datafile

0 1 D:ORACLEORADATATESTSYSTEM01.DBF

1 2 D:ORACLEORADATATESTRBS01.DBF

2 3 D:ORACLEORADATATESTUSERS01.DBF

3 4 D:ORACLEORADATATESTTEMP01.DBF

4 5 D:ORACLEORADATATESTTOOLS01.DBF

5 6 D:ORACLEORADATATESTINDX01.DBF

6 7 D:ORACLEORADATATESTDR01.DBF

四、生成dictv8.dll字典文件，生成字典文件的前提是system表空间必须没有损坏，据我的理解dict.ddl文件是根据TAB$和OBJ$等视图获取数据版本和数据库对象等信息，以便后面的unload。

1、在dul中执行scan database命令，会生成seg.dat、ext.dat、compatset.dat文件。

如；

D:tempdul>dul8

Data UnLoader 8.0.6.8 - Internal Use Only - on T

with 64-bit io functions

Copyright (c) 1994/2000 Bernard van Duijnen All

DUL: Warning: Recreating file "dul.log"

DUL> scan database ;

tablespace 0, data file 1: 30143 blocks scanned

tablespace 1, data file 2: 8959 blocks scanned

tablespace 2, data file 3: 2559 blocks scanned

tablespace 3, data file 4: 2559 blocks scanned

tablespace 4, data file 5: 1279 blocks scanned

tablespace 5, data file 6: 2559 blocks scanned

tablespace 6, data file 7: 2559 blocks scanned

D:tempdul>

2、重新启动dul工具,执行bootstrap命令，会在当前目录下生成bootstrap.ctl、bootstrap.dat两个文件;

如下：

DUL> bootstrap;

Compatibility segment found at file = 1, block = 173

database version 8 bootstrap$ at file 1, block 133

. unloading table BOOTSTRAP$ 54 rows unloaded

Restart DUL and proceed with "bootstrap generate;"

DUL>

3、再重新启动dul工具,执行bootstrap generate命令。

D:tempdul>dul8

Data UnLoader 8.0.6.8 - Internal Use Only - on Thu Jan 05 16:23:21 2006

with 64-bit io functions

Copyright (c) 1994/2000 Bernard van Duijnen All rights reserved.

DUL: Warning: Recreating file "dul.log"

Loaded 653 entries from SEG.dat

Loaded 1366 entries from EXT.dat

Sorted 1366 entries

Loaded 1 entries from COMPATSEG.dat

Loaded 54 entries from BOOTSTRAP.dat

DUL> bootstrap generate;

Parsing Bootstrap$ contents

DUL: Warning: Dictionary cache not loaded, can only generate base tables

Generating dict.ddl

OBJ$: segobjno 18, file 1

TAB$: segobjno 2, tabno 1, file 1

COL$: segobjno 2, tabno 5, file 1

USER$: segobjno 10, tabno 1, file 1

Please do "@dict.ddl" to unload the dictionary tables

DUL>

在当前目录下生成dict.ddl文件，文件内容如下 ；

REM DDL Script to unload the dictionary cache for DUL8

REM force the settings to get the expected readable format

alter session set export_mode = false;

alter session set ldr_phys_rec_size = 0;

alter session set ldr_enclose_char = """" ;

alter session set file = "" ;

unload table OBJ$( OBJ# number, DATAOBJ# ignore, OWNER# number,

NAME varchar2(30), NAMESPACE ignore, SUBNAME varchar2(30), TYPE# number)

storage( segobjno 18 file 1);

unload table TAB$( OBJ# number, DATAOBJ# number,

TS# number, FILE# number, BLOCK# number,

BOBJ# number, TAB# number, COLS number, CLUCOLS number,

pctfree$ ignore, pctused$ ignore, initrans ignore, maxtrans ignore,

flags ignore, audit$ ignore, rowcnt ignore, blkcnt ignore,

empcnt ignore, avgspc ignore, chncnt ignore, avgrln ignore,

avgspc_flb ignore, flbcnt ignore,

analyzetime ignore, samplesize ignore,

degree ignore, instances ignore,

intcols ignore, kernelcols ignore, property number)

cluster C_OBJ#(OBJ#)

storage ( segobjno 2 tabno 1 file 1);

unload table COL$ ( OBJ# number, COL# number , SEGCOL# number,

SEGCOLLENGTH ignore, OFFSET ignore, NAME char(30),

TYPE# number, LENGTH number, FIXEDSTORAGE ignore,

PRECISION# ignore, SCALE ignore, NULL$ ignore, DEFLENGTH ignore,

DEFAULT$ ignore, INTCOL# number, PROPERTY number)

cluster C_OBJ#(OBJ#)

storage(segobjno 2 tabno 5 file 1);

unload table USER$( USER# number, NAME varchar2(30))

cluster C_USER#(USER#)

storage( segobjno 10 tabno 1 file 1);

ECHO exit and restart DUL to load the first four dictionary tables in the cache

ECHO rerun "bootstrap generate;" to locate all dictionary tables.

4、 执行生成的dict.ddl文件。

DUL> @dict.ddl

Parameter altered

Parameter altered

Parameter altered

Parameter altered

. unloading table OBJ$ 21919 rows unloaded

. unloading table TAB$ 308 rows unloaded

. unloading table COL$ 17390 rows unloaded

. unloading table USER$ 39 rows unloaded

exit and restart DUL to load the first four dictionary tables in the cache

rerun "bootstrap generate;" to locate all dictionary tables.

DUL>

五、利用生成的dict.ddl执行dul工具，dul工具会自动寻找当前目录下由dict.ddl文件脚本生成的相关脚本。如col.dat、col.clt等文件，确定当前数据库那些数据库对象。

D:tempdul>dul8

Data UnLoader 8.0.6.8 - Internal Use Only - on Thu Jan 05 16:39:22 2006

with 64-bit io functions

Copyright (c) 1994/2000 Bernard van Duijnen All rights reserved.

DUL: Warning: Recreating file "dul.log"

Loaded 39 entries from USER.dat

Loaded 21919 entries from OBJ.dat

Loaded 308 entries from TAB.dat

Loaded 17390 entries from COL.dat

Loaded 653 entries from SEG.dat

Loaded 1366 entries from EXT.dat

Sorted 1366 entries

Loaded 0 entries from TABPART.dat

Loaded 379 entries from IND.dat

Loaded 23 entries from LOB.dat

Loaded 710 entries from ICOL.dat

Loaded 1 entries from COMPATSEG.dat

Loaded 54 entries from BOOTSTRAP.dat

DUL>

六、使用unload命令导出数据。支持几种方式：

Unload database 整个数据库

Unload user 导出用户对象

Unload table 导出单个表

这里我使用unload user方式。

过程如下：

DUL> unload user zlhis;

About to unload ZLHIS's tables ...

. unloading table TEST_A 21812 rows unloaded

. unloading table TEST_B 21813 rows unloaded

. unloading table TEST_C 21814 rows unloaded

DUL>

会在当前目录下生成几个三个dmp文件，分别对应zhis用户的三张表。

七、验证证结果，将dmp 文件使用imp工作导入到数据库。

附：unload database的情况：

DUL> unload database;

About to unload PUBLIC's tables ...

About to unload CONNECT's tables ...

About to unload RESOURCE's tables ...

About to unload DBA's tables ...

About to unload SYSTEM's tables ...

. unloading table AQ$_QUEUE_TABLES 2 rows unloaded

. unloading table AQ$_QUEUES

DUL: Error: Column SUBSCRIBERS of type HEXRAW cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

4 rows unloaded

. unloading table AQ$_SCHEDULES 0 rows unloaded

DUL: Warning: Not all BLOBS work right now.

. unloading table DEF$_AQCALL

DUL: Error: Column USER_DATA of type BLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

DUL: Warning: Not all BLOBS work right now.

. unloading table DEF$_AQERROR

DUL: Error: Column USER_DATA of type BLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

. unloading table DEF$_ERROR 0 rows unloaded

. unloading table DEF$_DESTINATION 0 rows unloaded

. unloading table DEF$_CALLDEST 0 rows unloaded

. unloading table DEF$_DEFAULTDEST 0 rows unloaded

DUL: Warning: Not all BLOBS work right now.

DUL: Warning: Not all CLOBS work right now.

DUL: Warning: Not all CLOBS work right now.

. unloading table DEF$_LOB

DUL: Error: Column BLOB_COL of type BLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

DUL: Error: Column CLOB_COL of type CLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

DUL: Error: Column NCLOB_COL of type CLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

DUL: Warning: Not all BLOBS work right now.

DUL: Warning: Not all CLOBS work right now.

DUL: Warning: Not all CLOBS work right now.

. unloading table DEF$_TEMP$LOB

DUL: Error: Column TEMP$BLOB of type BLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

DUL: Error: Column TEMP$CLOB of type CLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

DUL: Error: Column TEMP$NCLOB of type CLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

. unloading table DEF$_PROPAGATOR 0 rows unloaded

. unloading table DEF$_ORIGIN 0 rows unloaded

. unloading table DEF$_PUSHED_TRANSACTIONS 0 rows unloaded

. unloading table SQLPLUS_PRODUCT_PROFILE 0 rows unloaded

. unloading table REPCAT$_REPCAT 0 rows unloaded

. unloading table REPCAT$_FLAVORS 0 rows unloaded

. unloading table REPCAT$_REPSCHEMA 0 rows unloaded

. unloading table REPCAT$_SNAPGROUP 0 rows unloaded

. unloading table REPCAT$_REPOBJECT 0 rows unloaded

. unloading table REPCAT$_REPCOLUMN 0 rows unloaded

. unloading table REPCAT$_KEY_COLUMNS 0 rows unloaded

. unloading table REPCAT$_GENERATED 0 rows unloaded

. unloading table REPCAT$_REPPROP 0 rows unloaded

. unloading table REPCAT$_REPCATLOG 0 rows unloaded

. unloading table REPCAT$_DDL 0 rows unloaded

. unloading table REPCAT$_REPGROUP_PRIVS 0 rows unloaded

. unloading table REPCAT$_PRIORITY_GROUP 0 rows unloaded

. unloading table REPCAT$_PRIORITY 0 rows unloaded

. unloading table REPCAT$_COLUMN_GROUP 0 rows unloaded

. unloading table REPCAT$_GROUPED_COLUMN 0 rows unloaded

. unloading table REPCAT$_CONFLICT 0 rows unloaded

. unloading table REPCAT$_RESOLUTION_METHOD 19 rows unloaded

. unloading table REPCAT$_RESOLUTION 0 rows unloaded

. unloading table REPCAT$_RESOLUTION_STATISTICS 0 rows unloaded

. unloading table REPCAT$_RESOL_STATS_CONTROL 0 rows unloaded

. unloading table REPCAT$_PARAMETER_COLUMN 0 rows unloaded

. unloading table REPCAT$_AUDIT_ATTRIBUTE 2 rows unloaded

. unloading table REPCAT$_AUDIT_COLUMN 0 rows unloaded

. unloading table REPCAT$_FLAVOR_OBJECTS 0 rows unloaded

. unloading table REPCAT$_REFRESH_TEMPLATES 0 rows unloaded

. unloading table REPCAT$_USER_AUTHORIZATIONS 0 rows unloaded

DUL: Warning: Not all CLOBS work right now.

. unloading table REPCAT$_TEMPLATE_OBJECTS

DUL: Error: Column DDL_TEXT of type CLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

DUL: Warning: Not all CLOBS work right now.

. unloading table REPCAT$_TEMPLATE_PARMS

DUL: Error: Column DEFAULT_PARM_VALUE of type CLOB cannot be unloaded in export

mode

DUL: Warning: Column will be ignored

0 rows unloaded

. unloading table REPCAT$_OBJECT_PARMS 0 rows unloaded

DUL: Warning: Not all CLOBS work right now.

. unloading table REPCAT$_USER_PARM_VALUES

DUL: Error: Column PARM_VALUE of type CLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

. unloading table REPCAT$_TEMPLATE_SITES 0 rows unloaded

DUL: Warning: Not all CLOBS work right now.

. unloading table REPCAT$_RUNTIME_PARMS

DUL: Error: Column PARM_VALUE of type CLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

. unloading table HELP 816 rows unloaded

About to unload SELECT_CATALOG_ROLE's tables ...

About to unload EXECUTE_CATALOG_ROLE's tables ...

About to unload DELETE_CATALOG_ROLE's tables ...

About to unload EXP_FULL_DATABASE's tables ...

About to unload IMP_FULL_DATABASE's tables ...

About to unload OUTLN's tables ...

. unloading table OL$ 0 rows unloaded

. unloading table OL$HINTS 0 rows unloaded

About to unload RECOVERY_CATALOG_OWNER's tables ...

About to unload AQ_ADMINISTRATOR_ROLE's tables ...

About to unload AQ_USER_ROLE's tables ...

About to unload SNMPAGENT's tables ...

About to unload DBSNMP's tables ...

About to unload OEM_MONITOR's tables ...

About to unload HS_ADMIN_ROLE's tables ...

About to unload JAVAUSERPRIV's tables ...

About to unload JAVAIDPRIV's tables ...

About to unload _NEXT_USER's tables ...

About to unload JAVASYSPRIV's tables ...

About to unload JAVADEBUGPRIV's tables ...

About to unload JAVA_ADMIN's tables ...

About to unload AURORA$ORB$UNAUTHENTICATED's tables ...

About to unload ORDSYS's tables ...

. unloading table ORD_INSTALLATIONS 5 rows unloaded

. unloading table ORD_CARTRIDGE_COMPONENTS 165 rows unloaded

DUL: Warning: Not all BLOBS work right now.

. unloading table CREATE$JAVA$LOB$TABLE

DUL: Error: Column LOB of type BLOB cannot be unloaded in export_mode

DUL: Warning: Column will be ignored

0 rows unloaded

. unloading table JAVA$CLASS$MD5$TABLE 159 rows unloaded

. unloading table ORDT_TIMESERIES 0 rows unloaded

. unloading table ORDT_TIMESERIES_OBJS 0 rows unloaded

. unloading table ORDT_FLAT_ATTRIBUTES 0 rows unloaded

. unloading table ORDT_OBJECT_ATTRIBUTES 0 rows unloaded

. unloading table ORDT_TIMESERIES_COLS 0 rows unloaded

About to unload ORDPLUGINS's tables ...

About to unload MDSYS's tables ...

. unloading table MD$RELATE 90 rows unloaded

. unloading table OGIS_SPATIAL_REFERENCE_SYSTEMS 0 rows unloaded

. unloading table OGIS_GEOMETRY_COLUMNS 0 rows unloaded

. unloading table MD$TAB 0 rows unloaded

. unloading table MD$PTS 0 rows unloaded

. unloading table MD$PTAB 0 rows unloaded

. unloading table MD$COL

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40976/viewspace-903321/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40976/viewspace-903321/