1、Data Pump
Data Pump生成的文件具有下列3种形式:SQL文件、转储文件以及日志文件。
Data Pump在一个Oracle目录中读、写文件。DBA在数据库内创建一个目录,这个目录指向操作系统文件系统内的一个物理路径。Oracle目录始终属于用户SYS,但为了创建目录,必须授权“CREATE DIRECTORY”。
Data Pump导出数据:
############################################### # 修改参数文件,在最后一行加上导出目录 UTL_FILE_DIR=/dp_dir # 因为oracle用户没有创建目录权限,用root创建,再授权给oracle ############################################### $ vi $ORACLE_HOME/dbs/initora.ora $ su 口令: # mkdir /dp_dir # chown oracle:oinstall /dp_dir # su oracle |
|
$ expdp test/test schemas=test directory=dp_dir dumpfile=test; Export: Release 10.2.0.1.0 - Production on Friday, 26 September, 2008 15:37:36 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/******** schemas=test directory=dp_dir dumpfile=test Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT . . exported "TEST"."DP_TEST" 5.929 KB 21 rows Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ************************************************************************ Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is: /dp_dir/test.dmp Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:38:00 |
Data Pump导入数据:
|
$ impdp userid=test/test dumpfile=test.dmp directory=dp_dir; Import: Release 10.2.0.1.0 - Production on Friday, 26 September, 2008 16:04:02 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "TEST"."SYS_IMPORT_FULL_01": userid=test/******** dumpfile=test.dmp directory=dp_dir Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "TEST"."DP_TEST" 5.929 KB 21 rows Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 16:04:09 |
|
导出、导入都成功!!!耶~
2、SQL*Loader
需要批量上载第三方系统所生成的数据集,我们就可以用SQL*Loader。
# chown -R oracle:oinstall /ora01 # chmod -R 777 /ora01 # su oracle $ vi /ora01/test.ctl load data infile '/ora01/test.bat' append into table dp_test fields terminated by ',' (username,user_id) $ vi /ora01/test.bat hn,555 Damir,666 John,777 $ sqlldr userid=test/test control=/ora01/test.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Sep 26 17:45:22 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 4 |
OK,加载成功!不行,进入查看,会发现,多了三条记录了。
|