A-A+

Data Pump Export/Impdp操作实例

2013年01月14日 Backup&Recovery, Database 评论 1 条 阅读 1,763 次

Data Pump Export/Impdp操作实例
（一）Expdp相关    expdp是将数据或者元数据从数据库卸载到dump文件集的一个工具，而这种dump文件集也只能由impdp工具使用，在相同的系统或者将她移动到别的系统进行导入操作。
dump文件集是由一个或多个磁盘文件组成，里边包含数据库对象元数据和控制信息。这些文件有自己自由的格式的二进制文件。在一个impdp导入操作中，将会使用到这谢文件集。
由于dump文件集是由服务器端来写的，而不是用客户端来写的，所以需要dba创建一个目录对象。
要执行一个导出操作，可以用带参数的expdp命令行，或者将参数写到一个参数文件，再用expdp命令调用，或者使用命令行交互式的使用。
注意：由于sysdba拥有自己特有的内部函数，并且其将产生的行为也和一般的用户不同，所以不要以sysdba的角色执行expdp。

expdp的参数说明
在命令行和参数文件中可以用以下参数
注：userid必须在所有参数前边
COMPRESSION           压缩dump文件大小
CONTENT               指定卸载方式:默认全部(ALL)，只导出数据（DATA_ONLY）,只导出元数据（METADATA_ONLY）.
DIRECTORY             指定目录
DUMPFILE              指定dump文件名
ENCRYPTION_PASSWORD   指定密钥
ESTIMATE_ONLY         只估算输出文件的大小.
EXCLUDE               排除指定的对象 e.g. EXCLUDE=TABLE:EMP.
FILESIZE              指定每个dump文件的大小
FULL                  导出整个数据库库(N).
HELP                  查看帮助信息 (N).
INCLUDE               包括指定的对象 e.g. INCLUDE=TABLE_DATA.
JOB_NAME              创建job名字.
LOGFILE               指定日志文件名
NOLOGFILE             不指定日志文件 (N).
PARALLEL              设置并行度.
PARFILE               指定参数文件.
QUERY                 使用查询子句导出表的部分数据.
SAMPLE                导出表的样本数据;
SCHEMAS               导出指定的schema(login schema).
STATUS                多长时间显示一次job的状态default (0).
TABLES                指定表
TABLESPACES           指定表空间
TRANSPORT_TABLESPACES 指定表空间的元数据需要导出.

常用导出实例
 1.关于目录
 查看当前目录设置
SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/product/10.2.0/db_1/rdbms/log/

 创建自定义的目录
[oracle@ora10gr2 /]$ cd $ORACLE_BASE
[oracle@ora10gr2 oracle]$ pwd
/u01/app/oracle
[oracle@ora10gr2 oracle]$ cd
[oracle@ora10gr2 ~]$ mkdir $ORACLE_BASE/dump_dir1
[oracle@ora10gr2 ~]$ exit
exit

SQL> CREATE DIRECTORY DUMP_FILES1 AS '/u01/app/oracle/dump_dir1';

Directory created.
SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_FILES1';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
DUMP_FILES1                    /u01/app/oracle/dump_dir1

注：在以下的实验中，将使用自定义的目录dump_files1

2.预估全库导出文件的大小
[oracle@ora10gr2 ~]$ expdp system/oracle ESTIMATE_ONLY=y full=y

Export: Release 10.2.0.1.0 - Production on Sunday, 13 January, 2013 20:26:01

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** ESTIMATE_ONLY=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
.  estimated "OGG"."GGS_DDL_HIST"                            5 MB
省略部分输出...
Total estimation using BLOCKS method: 19.18 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 20:26:28

3.全库导出
注意：全库导出不会包含系统用户的对象。
[oracle@ora10gr2 ~]$ expdp system/oracle directory=dump_files1 dumpfile=full_exp.dump full=y logfile=full_exp.log

Export: Release 10.2.0.1.0 - Production on Sunday, 13 January, 2013 20:05:53

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=dump_files1 dumpfile=full_exp.dump full=y logfile=full_exp.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 15.68 MB
Processing object type DATABASE_EXPORT/TABLESPACE
省略部分输出...
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
. . exported "OGG"."GGS_DDL_HIST"                        558.4 KB     206 rows
. . exported "SYSMAN"."MGMT_METRICS"                     553.3 KB    2578 rows
. . exported "SYSMAN"."MGMT_HC_OS_COMPONENTS"            202.5 KB     720 rows
. . exported "SYSMAN"."MGMT_HC_VENDOR_SW_COMPONENTS"     202.5 KB     720 rows
省略部分输出...
. . exported "TEST"."TEST"                               5.546 KB       1 rows
省略部分输出...
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
. . exported "TSMSYS"."SRS$"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/dump_dir1/full_exp.dump
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 20:08:45

查看实际导出文件大小
[oracle@ora10gr2 ~]$ du -sm /u01/app/oracle/dump_dir1/full_exp.dump
16      /u01/app/oracle/dump_dir1/full_exp.dump

4.导出指定用户的所有数据
[oracle@ora10gr2 ~]$ expdp system/oracle directory=dump_files1 dumpfile=schema_exp.dump schemas=test logfile=schema_exp.log

Export: Release 10.2.0.1.0 - Production on Sunday, 13 January, 2013 20:15:04

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dump_files1 dumpfile=schema_exp.dump schemas=test logfile=schema_exp.log
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/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST"."TEST"                               5.546 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/dump_dir1/schema_exp.dump
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:15:23

5.导出指定用户表
[oracle@ora10gr2 ~]$ expdp system/oracle directory=dump_files1 dumpfile=table_exp.dump tables=test.test logfile=table_exp.log

Export: Release 10.2.0.1.0 - Production on Sunday, 13 January, 2013 20:16:53

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 "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dump_files1 dumpfile=table_exp.dump tables=test.test logfile=table_exp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TEST"                               5.546 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/dump_dir1/table_exp.dump
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 20:17:03

 6.导出表空间
[oracle@ora10gr2 ~]$ expdp system/oracle directory=dump_files1 dumpfile=tablespace_exp.dump tablespaces=test logfile=tablespce_exp.log

Export: Release 10.2.0.1.0 - Production on Sunday, 13 January, 2013 20:23:05

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 "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** directory=dump_files1 dumpfile=tablespace_exp.dump tablespaces=test logfile=tablespce_exp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TEST"                               5.546 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/u01/app/oracle/dump_dir1/tablespace_exp.dump
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 20:23:18

    7.使用参数文件导出用户指定表
[oracle@ora10gr2 ~]$ cat par.txt
directory=dump_files1
dumpfile=table_exp_1.dmp
logfile=table_exp_1.log
tables=test.test
[oracle@ora10gr2 ~]$ pwd
/home/oracle
[oracle@ora10gr2 ~]$ expdp system/oracle parfile='/home/oracle/par.txt'

Export: Release 10.2.0.1.0 - Production on Sunday, 13 January, 2013 20:40:50

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 "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=/home/oracle/par.txt
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TEST"                               5.546 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/dump_dir1/table_exp_1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 20:41:00

（二）Impdp相关
1.impdp和expdp类似，具体见expdp解释
imppdp的参数说明
大多数参数和expdp一致，下边列出不一致的比较有特点常用的参数
在命令行和参数文件中可以用以下参数
注：userid必须在所有参数前边
CONTENT               指定加载方式:默认全部(ALL)，只导出数据（DATA_ONLY）,只导出元数据（METADATA_ONLY）.
DIRECTORY             指定目录
DUMPFILE              指定dump文件名
ENCRYPTION_PASSWORD   指定密钥
EXCLUDE               排除指定的对象 e.g. EXCLUDE=TABLE:EMP.
FULL                  导出整个数据库库(N).
HELP                  查看帮助信息 (N).
INCLUDE               包括指定的对象 e.g. INCLUDE=TABLE_DATA.
JOB_NAME              创建job名字.
LOGFILE               指定日志文件名
NOLOGFILE             不指定日志文件 (N).
PARALLEL              设置并行度.
PARFILE               指定参数文件.
QUERY                 使用查询子句导出表的部分数据.
REMAP_DATAFILE        以ddl重新定义数据文件.
REMAP_SCHEMA          将一个schema的数据导入到另一个schema中.
REMAP_TABLESPACE      将表空间对象加载到其他表空间.
REUSE_DATAFILES       重新初始化已存在的数据文件 (N).
SKIP_UNUSABLE_INDEXES 跳过不可用的索引.
SQLFILE               将所有的ddl的sql写入到指定文件.
TABLE_EXISTS_ACTION   表已存在可以采取的措施:(SKIP), APPEND, REPLACE and TRUNCATE.
STATUS                多长时间显示一次job的状态default (0).
TABLES                指定表
TABLESPACES           指定表空间
TRANSPORT_TABLESPACES 指定表空间的元数据需要导入.

常用导入实例

 2.全库导入
模拟删除用户
SQL> select a.*,to_char(inserttime,'yyyy/mm/dd hh24:mi:ss') from test.test a;

ID NAME                 INSERTTIM TO_CHAR(INSERTTIME,
---------- -------------------- --------- -------------------
1 yallonking           13-JAN-13 2013/01/13 19:56:39

SQL> drop user test cascade;

User dropped.
[oracle@ora10gr2 ~]$ impdp system/oracle dumpfile=dump_files1:full_exp.dump full=y logfile=full_imp.log

Import: Release 10.2.0.1.0 - Production on Sunday, 13 January, 2013 22:59:40

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump_files1 dumpfile=full_exp.dump full=y logfile=full_imp.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-31684: Object type TABLESPACE:"TEST" already exists
省略部分输出...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2744 error(s) at 23:05:40

[oracle@ora10gr2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 13 23:12:08 2013

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

SQL> select a.*,to_char(inserttime,'yyyy/mm/dd hh24:mi:ss') from test.test a;

ID NAME                 INSERTTIM TO_CHAR(INSERTTIME,
---------- -------------------- --------- -------------------
1 yallonking           13-JAN-13 2013/01/13 19:56:39

    3.将用户表导入到其他用户下
SQL> conn /as sysdba
Connected.
SQL> select a.*,to_char(inserttime,'yyyy/mm/dd hh24:mi:ss') from test.test a;

ID NAME                 INSERTTIM TO_CHAR(INSERTTIME,
---------- -------------------- --------- -------------------
1 yallonking           13-JAN-13 2013/01/13 19:56:39

SQL> select a.*,to_char(inserttime,'yyyy/mm/dd hh24:mi:ss') from test1.test a;
select a.*,to_char(inserttime,'yyyy/mm/dd hh24:mi:ss') from test1.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
此处将表test的数据导入到用户test下
[oracle@ora10gr2 ~]$ impdp system/oracle dumpfile=dump_files1:full_exp.dump REMAP_SCHEMA=test:test1 logfile=user_imp1.log
或者从导出的用户数据的dump导入
[oracle@ora10gr2 ~]$ impdp system/oracle dumpfile=dump_files1:schema_exp.dump REMAP_SCHEMA=test:test1 logfile=schema_imp.log
或者从导出的表数据的dump导入
[oracle@ora10gr2 ~]$ impdp system/oracle dumpfile=dump_files1:table_exp.dump REMAP_SCHEMA=test:test1 logfile=table_imp.log
Import: Release 10.2.0.1.0 - Production on Monday, 14 January, 2013 0:56: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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=dump_files1:table_exp.dump REMAP_SCHEMA=test:test1 logfile=table_imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST1"."TEST"                              5.546 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 00:56:24

SQL> select a.*,to_char(inserttime,'yyyy/mm/dd hh24:mi:ss') from test1.test a;

ID NAME                 INSERTTIM TO_CHAR(INSERTTIME,
---------- -------------------- --------- -------------------
1 yallonking           13-JAN-13 2013/01/13 19:56:39

  4.导入用户表到别的表空间
首先查看test表在那个表空间，并新建一个表空间test1
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME='TEST';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           TEST                           TEST

SQL> create tablespace test1 datafile '/tmp/test1.dbf' size 10m;

Tablespace created.
SQL> conn test/test
Connected.
SQL> drop table test;

Table dropped.
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME='TEST';

no rows selected
[oracle@ora10gr2 ~]$ impdp system/oracle directory=dump_files1 dumpfile=tablespace_exp.dump REMAP_TABLESPACE=test:test1

Import: Release 10.2.0.1.0 - Production on Monday, 14 January, 2013 1:10:15

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump_files1 dumpfile=tablespace_exp.dump REMAP_TABLESPACE=test:test1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TEST"                               5.546 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 01:10:36

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME='TEST';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           TEST                           TEST1

标签：

1 条留言  访客：0 条  博主：0 条   引用： 1 条

来自外部的引用： 1 条

  • 数据库高版本到低版本跨操作系统位数跨用户的数据导入导出 | YallonKing

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到：