实验环境: 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/