expdp/impdp的使用方法

1、环境准备：

(1)检查有无建立expdp目录

SQL>sqlplus / as sysdba

SQL>desc dba_directories;

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                                     VARCHAR2(4000)

SQL>set linesize 200
SQL>col owner format a8;
SQL>col DIRECTORY_NAME format a30
SQL>col DIRECTORY_PATH format a70

SQL>select * from dba_directories;

OWNER DIRECTORY_NAME            DIRECTORY_PATH
----- ------------------------- ----------------------------------------------------------------------
SYS   LOG_DIR                   /home/data/file/real
SYS   IDR_DIR                   /Oracle/app/diag/rdbms/oradb/oradb/ir
SYS   SUBDIR                    /Oracle/app/product/11.1.0/db/demo/schema/order_entry//2002/Sep
SYS   XMLDIR                    /Oracle/app/product/11.1.0/db/demo/schema/order_entry/
SYS   LOG_FILE_DIR              /Oracle/app/product/11.1.0/db/demo/schema/log/
SYS   DATA_FILE_DIR             /Oracle/app/product/11.1.0/db/demo/schema/sales_history/
SYS   MEDIA_DIR                 /Oracle/app/product/11.1.0/db/demo/schema/product_media/
SYS   AUDIT_DIR                 /tmp/
SYS   DATA_PUMP_DIR             /Oracle/app/admin/oradb/dpdump/
SYS   ORACLE_OCM_CONFIG_DIR     /Oracle/app/product/11.1.0/db/ccr/state

10 rows selected.

结果：没有expdp目录。

(2)建立目录，并赋给用户权限。

[oracle@anpc ~]$ mkdir /Oracle/expdp

SQL>sqlplus / as sysdba

SQL> create or replace directory expdp_dir as '/Oracle/expdp';

Directory created.

SQL> grant read,write on directory expdp_dir to scott;

Grant succeeded.

SQL> select * from dba_directories where DIRECTORY_NAME='EXPDP_DIR';

OWNER DIRECTORY_NAME            DIRECTORY_PATH
----- ------------------------- ----------------------------------------------------------------------
SYS   EXPDP_DIR                 /Oracle/expdp

2、脚本测试：

(1)导出备份脚本如下：

[oracle@anpc ~]$vi expdptest.sh
#!/bin/bash
#expdp backup database
#mady by andy

f_name=`date +%Y%m%d`
export f_name

date +"%Y-%m-%d %H:%M:%S"

expdp system/system@oradb dumpfile=scott_expdp_%U_$f_name.dmp logfile=scott_expdp_$f_name.log directory=expdp_dir SCHEMAS=scott filesize=500m

parallel=4

date +"%Y-%m-%d %H:%M:%S"

[oracle@anpc ~]$chmod +x expdptest.sh
[oracle@anpc ~]$./expdptest.sh

(2)手动执行导出脚本测试。
[oracle@anpc ~]$ ./expdptest.sh
2011-06-01 14:39:24

Export: Release 11.1.0.6.0 - 64bit Production on Wednesday, 01 June, 2011 14:39:24

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@oradb dumpfile=scott_expdp_%U_20110601.dmp logfile=scott_expdp_20110601.log

directory=expdp_dir SCHEMAS=scott filesize=500m parallel=4
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
Processing object type SCHEMA_EXPORT/USER
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."TEST"                              5.531 KB       9 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /Oracle/expdp/scott_expdp_01_20110601.dmp
  /Oracle/expdp/scott_expdp_02_20110601.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:39:33

2011-06-01 14:39:33

(4)导入脚本如下：

[oracle@anpc ~]$vi impdptest.sh
#!/bin/bash
#impdp into database
#mady by andy

f_name=`date +%Y%m%d`
export f_name

date +"%Y-%m-%d %H:%M:%S"

impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_01_$f_name.dmp,scott_expdp_02_$f_name.dmp logfile=scott_impdp_$f_name.log  

remap_schema=scott:scott

date +"%Y-%m-%d %H:%M:%S"

[oracle@anpc ~]$chmod +x impdptest.sh

(5)执行导入脚本：

[oracle@anpc ~]$./impdptest.sh
2011-06-01 16:07:53

Import: Release 11.1.0.6.0 - 64bit Production on Wednesday, 01 June, 2011 16:07:53

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@oradb directory=expdp_dir

dumpfile=scott_expdp_01_20110601.dmp,scott_expdp_02_20110601.dmp logfile=scott_impdp_20110601.log remap_schema=scott:scott
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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 "SCOTT"."DEPT"                              5.937 KB       4 rows
. . imported "SCOTT"."EMP"                               8.570 KB      14 rows
. . imported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . imported "SCOTT"."TEST"                              5.531 KB       9 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:07:56

2011-06-01 16:07:57

测试登陆是否成功：
[oracle@anpc ~]$ sqlplus scott/scott@oradb

3、expdp/impdp导入导出模式：

(1)全库导出导入模式

expdp system/system@oradb directory=expdp_dir dumpfile=expdp_fulldb_$f_name.dmp  full=y

impdp system/system@oradb directory=expdp_dir dumpfile=expdp_fulldb_$f_name.dmp  full=y

(2)用户导出模式

expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp SCHEMAS=scott

impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_01_$f_name.dmp,scott_expdp_02_$f_name.dmp   remap_schema=scott:scott

(3)表导出模式

expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp tables=scott.emp, scott.dept
注：使用query条件的语句时最好采用parfile方式可以按条件导出数据。

impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp tables=scott.emp, scott.dept

impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp schemas=scott table_exists_action=append  
注：追加数据。

(4)表空间导出模式

expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp tablespaces=tbs1,tbs2

impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_01_$f_name.dmp tablespaces=tbs1,tbs2

(5)可移动表空间导出模式

expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp transport_tablespaces=tbs1


注：expdp -help可以查看到所有expdp的参数。
    impdp -help可以查看到所有impdp的参数。

常见参数：
DIRECTORY:指定转储文件和日志文件所在的目录
DUMPFILE:用于指定转储文件的名称,默认名称为expdat.dmp
LOGFILE:指定导出日志文件文件的名称,默认名称为export.log
SCHEMAS:该方案用于指定执行方案模式导出,默认为当前用户方案.
TABLESPACES:指定要导出表空间列表
TABLES:指定表模式导出.TABLES=[schema_name.]table_name[:partition_name][,…]

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}  ALL将导出对象定义及其所有数据，DATA_ONLY只导出对象数据，METADATA_ONLY只导出对象定义。
EXCLUDE=object_type[:name_clause] [,….]  排除的对象类型及相关对象。
INCLUDE=object_type[:name_clause] [,… ]  包含的对象类型及相关对象。不能和EXCLUDE同时用。
FILESIZE:指定导出文件的最大尺寸,默认为0,(表示文件尺寸没有限制)。
PARALLEL:指定执行导出操作的并行进程个数,默认值为1。

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

转载于:http://blog.itpub.net/24070945/viewspace-696900/