数据导入导出 expdp/impdp

最新推荐文章于 2021-11-01 17:03:47 发布

juliaputao

最新推荐文章于 2021-11-01 17:03:47 发布

阅读量615

点赞数

分类专栏： DATABASE

DATABASE 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

expdp/impdp使用限制：

1.Data Pump Import will not work with databases that are older than the Oracle Database 10g Release 1 (10.1) version. 
2.Also note that the new Data Pump technology lets you export data only to disk. 
You cannot use a tape drive when performing a Data Pump export.1.创建dump directory 
3.is a server-side infrastructure for fast data movement between Oracle databases。


优点：

提高速度，特别使用并行技术，很大的提高速度。

不同平台之间oracle 数据库 导入。



 [oracle@julia01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 23 07:31:42 2013

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

SQL> conn /as sysdba
Connected.
SQL> create or replace directory expdir as '/u01/backup/'  
  2  ;

Directory created.
2.赋予权限给impdp/expdp操作的用户
SQL> grant read,write on directory expdir to scott;

Grant succeeded.
3.导出一个schema下的所有对象
[oracle@julia01 backup]$ expdp scott/julia033 dumpfile=scott.dmp directory=expdir;

Export: Release 11.2.0.1.0 - Production on Sat Mar 23 07:35:27 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** dumpfile=scott.dmp directory=expdir 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18 MB
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TT"                                6.986 MB   72135 rows
. . exported "SCOTT"."TT_RECOV"                          6.994 MB   72234 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 07:36:08

按照查询条件导出：

scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

按照表空间导出：

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp；

导出整个数据库：

expdp system/julia033 directory=expdir dumpfile=full_%U.dump full=yes

获取帮助文档

expdp help=y or impdp help=y

expdp排除部分对象的导出：

exclude:排除特定的对象类型，例如exclude=table:emp

注意：

"
,
(
前面都要加\,才可以在linux下。

写文档的人，该炒鱿鱼了。

 expdp scott/julia033 dumpfile=dump%U directory=expdir exclude=table:\"IN\(\'TEST\'\)\"

Export: Release 11.2.0.1.0 - Production on Sat Mar 23 20:33:04 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** dumpfile=dump%U directory=expdir exclude=table:"IN('TEST')" 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18 MB
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TT"                                6.986 MB   72135 rows
. . exported "SCOTT"."TT_RECOV"                          6.994 MB   72234 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/dump01.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:33:28

[oracle@julia01 ~]$ expdp scott/julia033 dumpfile=dump_%U directory=expdir include=table:\"IN\(\'TEST\'\)\"

Export: Release 11.2.0.1.0 - Production on Sat Mar 23 20:34:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** dumpfile=dump_%U directory=expdir include=table:"IN('TEST')" 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TEST"                              5.450 MB   55627 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/dump_01.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:34:34

把参数写到参数文件里

[oracle@julia01 ~]$ more parfile.par 
DIRECTORY = expdir
    DUMPFILE = exp_scott_%U.dmp
    LOGFILE = exp_scott.log
    SCHEMAS = scott
    PARALLEL= 2
    EXCLUDE = TABLE:"IN ('EMP', 'DEPT')" -------如果是写在参数文件里，就不需要在‘ “ ）前加转义字符。

expdp \'/as sysdba \' parfile=parfile.par

EXCLUDE=SEQUENCE,VIEW                          --过滤所有的SEQUENCE,VIEW

EXCLUDE=TABLE:"IN ('EMP','DEPT')"               --过滤表对象EMP,DEPT

EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT

EXCLUDE=INDEX:"= 'INDX_NAME'"                   --过滤指定的索引对象INDX_NAME

INCLUDE=PROCEDURE:"LIKE 'PROC_U%'"              --包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符)

INCLUDE=TABLE:"> 'E' "                          --包含大于字符E的所有表对象

其它常用操作符 NOT IN, NOT LIKE, <, != 等等
直接将过滤操作符封装到参数文件中，如下面的例子

Parameter file:exp_scott.par

   DIRECTORY = dump_scott
   DUMPFILE = exp_scott_%U.dmp
   LOGFILE = exp_scott.log
    SCHEMAS = scott
    PARALLEL= 2
    EXCLUDE = TABLE:"IN ('EMP', 'DEPT')"

导出数据：

按照用户导出：

expdp \'/ as sysdba\' schemas=scott  dumpfile=scottschedmas.all_%U.dmp directory=expdir  ;

按照查询条件导出：

scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

按照表空间导出：

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp；

导出整个数据库：

expdp system/julia033 directory=expdir dumpfile=full_%U.dump full=yes

导入数据：

导入到指定用户下：

[oracle@julia01 backup]$ impdp \'/as sysdba\'  directory=expdir dumpfile=scott.test_01.dump  remap_schema=scott:julia

Import: Release 11.2.0.1.0 - Production on Sun Mar 24 02:37:23 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=expdir dumpfile=scott.test_01.dump remap_schema=scott:julia 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JULIA"."TEST"                              5.450 MB   55627 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 02:37:25

在不同的数据库之间移动表空间。要确保两个数据库之间的字符编码要一样

传输表空间

http://blog.csdn.net/juliaputao/article/details/8728304

SQL> /

PLATFORM_ID PLATFORM_NAME		   ENDIAN_FORMAT
----------- ------------------------------ --------------
	  1 Solaris[tm] OE (32-bit)	   Big
	  2 Solaris[tm] OE (64-bit)	   Big
	  3 HP-UX (64-bit)		   Big
	  4 HP-UX IA (64-bit)		   Big
	  5 HP Tru64 UNIX		   Little
	  6 AIX-Based Systems (64-bit)	   Big
	  7 Microsoft Windows IA (32-bit)  Little
	  8 Microsoft Windows IA (64-bit)  Little
	  9 IBM zSeries Based Linux	   Big
	 10 Linux IA (32-bit)		   Little
	 11 Linux IA (64-bit)		   Little

PLATFORM_ID PLATFORM_NAME		   ENDIAN_FORMAT
----------- ------------------------------ --------------
	 12 Microsoft Windows x86 64-bit   Little
	 13 Linux x86 64-bit		   Little
	 15 HP Open VMS 		   Little
	 16 Apple Mac OS		   Big
	 17 Solaris Operating System (x86) Little
	 18 IBM Power Based Linux	   Big
	 19 HP IA Open VMS		   Little
	 20 Solaris Operating System (x86- Little
	    64)

	 21 Apple Mac OS (x86-64)	   Little

20 rows selected.

SQL> l
  1* select * from v$transportable_platform order by platform_id
SQL>

juliaputao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据导入导出 expdp/impdp

1.创建dump directory [oracle@julia01 ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 23 07:31:42 2013Copyright (c) 1982, 2009, Oracle. All rights reserved.SQL> conn /as sys
复制链接

扫一扫

专栏目录