DATA PUMP

数据泵 学习心得 (from:http://blog.sina.com.cn/hellohuangqian)

ORACLE 10g 开始提供了2个实用的工具以方便导入导出工作.分别是expdp/impdp
先说说我的理解吧,原来的exp/imp工具是属于client/server模式,单线程操作,容易引起i/o瓶颈和操作中断新的datapump 解决了这些问题,datapump可以由于在服务器端操作,并且可以增加并行度,以提高i/o性能.操作意外中断也可以从中断点恢复继续操作.比较实用.

具体简单实用方法
expdp
举例 expdp hr/hr directory=expdir dumpfile=hr.dmp
很简单吧.首先要设置相关的保存文件目录.我设置在/tmp下
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 9 17:12:06 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create or replace directory expdir
2 as '/tmp/';
Directory created.
SQL> select * from dba_directories where directory_name='EXPDIR';
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS EXPDIR
/tmp/

SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_PUMP_DIR
/oracle/10g/rdbms/log/
设置完成后,接下来给予相关目录提供权限
SQL> grant read,write on directory expdir to hr;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
完成后可以开始操作了
[oracle@localhost ~]$ expdp hr/hr dumpfile=hr.dmp directory=expdir
Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:15:33
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** dumpfile=hr.dmp directory=expdir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.085 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
. . exported "HR"."JOBS" 6.609 KB 19 rows
. . exported "HR"."JOB_HISTORY" 6.585 KB 10 rows
. . exported "HR"."LOCATIONS" 7.710 KB 23 rows
. . exported "HR"."REGIONS" 5.289 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/tmp/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:16:14
[oracle@localhost ~]$
OK 数据泵已经将数据导出完成了.
验证一下
[oracle@localhost ~]$ ll /tmp/hr.dmp
-rw-r----- 1 oracle oinstall 425984 10-09 17:16 /tmp/hr.dmp
[oracle@localhost ~]$
没有问题.完成.

可能看过上面的步骤发现命令好简单
果真如此?
如果大家输入下面就知道了
[oracle@localhost ~]$ expdp -help
其中对于关系到重启动和关闭等比较重要的参数是下面几个(影响expdp中断再次中断点使用)
continue_client
start_job
status
stop_job

老样子,用例子来介绍吧...想学的睁大眼睛看好了哦 (我来露一手)

首先启动刚才的数据库导出命令,并且赋予一个任务名
[oracle@localhost ~]$ expdp hr/hr dumpfile=hr.dmp directory=expdir job_name=expfull

Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:36:25

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."EXPFULL": hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB

好了,这个时候按 ctrl+c 退出交互界面放心,由于datapump已经提交了任务,退出界面不会造成任务停止,服务器会自己完成.
此时会进入export命令行模式
Export>
记得上面4个命令?开始用了.
停止任务可以用stop_job
Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes

[oracle@localhost ~]$

重新连进任务
[oracle@localhost ~]$ expdp hr/hr attach=expfull

Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:41:44

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: EXPFULL
Owner: HR
Operation: EXPORT
Creator Privs: FALSE
GUID: 58CEC4441662D8D6E040007F01007F57
Start Time: Thursday, 09 October, 2008 17:41:48
Mode: SCHEMA
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/hr.dmp
bytes written: 12,288

Worker 1 Status:
State: UNDEFINED

Export>

用satatus查看
Export> status

Job: EXPFULL
Operation: EXPORT
Mode: SCHEMA
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/hr.dmp
bytes written: 12,288

Worker 1 Status:
State: UNDEFINED

Export>
重新启动任务
[oracle@localhost ~]$ expdp hr/hr attach=expfull

Export: Release 10.2.0.4.0 - Production on Thursday, 09 October, 2008 17:44:47

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: EXPFULL
Owner: HR
Operation: EXPORT
Creator Privs: FALSE
GUID: 58CEE0828384E4F0E040007F01007F84
Start Time: Thursday, 09 October, 2008 17:44:50
Mode: SCHEMA
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/hr.dmp
bytes written: 12,288

Worker 1 Status:
State: UNDEFINED

Export> start_job

Export> continue_client
Job EXPFULLL has been reopened at Thursday, 09 October, 2008 17:44
Restarting "HR"."EXPFULLL": hr/******** dumpfile=hr.dmp directory=expdir job_name=expfull
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "HR"."EXPFULL" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00942: table or view does not exist
. . exported "HR"."COUNTRIES" 6.085 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
. . exported "HR"."JOBS" 6.609 KB 19 rows
. . exported "HR"."JOB_HISTORY" 6.585 KB 10 rows
. . exported "HR"."LOCATIONS" 7.710 KB 23 rows
. . exported "HR"."REGIONS" 5.289 KB 4 rows
Master table "HR"."EXPFULLL" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.EXPFULLL is:
/tmp/hr.dmp
Job "HR"."EXPFULLL" completed with 1 error(s) at 17:45:03


hr@BBK> conn test/test
Connected.
test@BBK> select table_name from user_tables;

no rows selected

test@BBK> create table t (id int);

Table created.

test@BBK> insert into t values(1);

1 row created.

test@BBK> insert into t values(2);

1 row created.

test@BBK> commit;

Commit complete.

test@BBK> select * from t;

ID
----------
1
2

test@BBK> conn /as sysdba
Connected.
sys@BBK> grant read,write on directory expdir to test;

Grant succeeded.

sys@BBK> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[ora10g@bbk254 oracle]$ expdp test/test dumpfile=test.dmp directory=expdir

Export: Release 10.2.0.1.0 - Production on Monday, 06 September, 2010 16:10:23

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/******** dumpfile=test.dmp directory=expdir
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"."T" 4.937 KB 2 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/tmp/test.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:10:39


[ora10g@bbk254 oracle]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 6 16:10:57 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn /as sysdba
Connected.
sys@BBK> conn test/test
Connected.
test@BBK> insert into t values(0);

1 row created.

test@BBK> commit;

Commit complete.

test@BBK> select * from t;

ID
----------
1
2
0

test@BBK> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[ora10g@bbk254 oracle]$ impdp test/test directory=expdir dumpfile=test.dmp tables=t table_exists_action=replace

Import: Release 10.2.0.1.0 - Production on Monday, 06 September, 2010 16:14:19

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_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=expdir dumpfile=test.dmp tables=t table_exists_action=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T" 4.937 KB 2 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 16:14:29

[ora10g@bbk254 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 6 16:15:02 2010
Copyright (c) 1982, 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

sys@BBK> conn test/test
Connected.
test@BBK> select * from t;

ID
----------
1
2

test@BBK>