但行好事
莫论前程❤

Oracle学习笔记–数据泵

前言

expdp和impdp是oracle数据库之间移动数据的工具。expdp和impdp只能在数据库服务端使用,不能在客户端使用。本文简单总结了expdp和impdp常用的命令。

完整的常用的一套oracle备份以及还原方案

  • 在新库中新建数据目录,没有特别说明在哪执行的语句都可在plsql中执行
create OR replace directory IMP_DIR as 'D:\20200217';
select * from dba_directories;
  • 将此目录的读写权限赋予所有用户,可以按照自己的具体情况单独赋予读或写给某个用户
grant read,write on directory IMP_DIR to eprkjs;   # eprkjs 用户名 
  • 创建表空间,初始大小1000M,自动增长,每次32M,没有最大值限制
create tablespace EPRKDATA datafile 'D:\oradata\EPRKDATA01.dbf' size 1000M autoextend on;
create tablespace EPRKDATA datafile 'D:\oradata\EPRKDATA01.dbf' size 1000M autoextend on next 32M maxsize unlimited;
  • 创建用户(eprkjs),指定表空间(EPRKDATA)
create user eprkjs identified by ep81274 default tablespace EPRKDATA;
  • 赋予用户权限
grant connect,resource to eprkjs;
alter user eprkjs quota unlimited on EPRKDATA;
alter user eprkjs quota unlimited on EPRKINDEX;
grant connect,resource to eprkjs;
grant dba to eprkjs;
  • 在旧库所在服务器上的cmd中执行,expdp后是旧库中要备份库的用户密码以及实例,directory指定导出的目录,dumpfile指定导出的文件名称,schemas要导出库用户名
expdp eprkjs/ep81274@ORCL schemas=eprkjs directory=IMP_DIR parallel=4 dumpfile=EPRKJS_20191114_%U.dmp logfile=EPRKJS_20191114.log 
  • 在新库所在服务器的cmd中执行,impdp 后是新库中已经创建好的用户账号密码实例,directory要导入文件所在目录,dumpfile要导入文件的名称
impdp eprkjs/ep81274@ORCL schemas=eprkjs directory=IMP_DIR parallel=4 dumpfile=EPRKJS_20191114_%U.dmp  logfile=EPRKJS20191213.log  

操作模式

  • 全库模式

导入或导出整个数据库,对应expdp/impdp命令中的full参数,只有拥有dba或者exp_full_database和imp_full_database权限的用户,才能执行。

  • Schema模式

    导入或导出Schema下的自有对象,对应impdp和expdp命令中的Schema参数。这是默认的操作模式。如果拥有dba或者exp_full_database和imp_full_database权限的用户执行的话,就可以导出或导入多个Schema中的对象。

  • 表模式

    导出指定的表或者表分区(如果有分区的话)以及依赖该表的对象(如该表的索引,约束等,不过前提是这些对象再同一个Schema中)或者执行的用户有相应的权限,对应impdp和expdp命令中的table参数。

  • 表空间模式

    导出指定表空间中的内容,对应tablespace参数。

  • 传输表空间模式

    对应transport_tablespace参数。该模式与前者之间的区别在于,生成的dump文件并不包含具体的逻辑数据,而只导出相关对象的元数据(即对象的定义,可以理解成表的创建语句),逻辑数据仍然再表空间的数据文件中,导出时需要将元数据hh额数据文件同时复制到目标端服务器。

    执行传输表空间模式的导入/导出,用户必须拥有dba或者exp_full_database和imp_full_database权限

EXPDP导出

注意:
1、导数的数据库用户需要拥有对directory_object的读写权限。
2、操作系统中需要已经存在directory_object指定的路径。
3、oracle用户拥有对directory_object指定路径的读写权限。
4、system用户导出用户,会将创建用户和授予系统权限的元数据也导出,普通用户不能导出这些元数据。

expdp命令示例

##导出一张表,例:
expdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=expdp.log tables=eprkjs.eprk_usercache

##导出多张表,例:
expdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=expdp.log tables=(eprkjs.eprk_usercache,eprkjs.eprk_billtype)

##导出一个用户(导出这个用户的所有对象),例:
expdp eprkjs/ep81274 schemas=eprkjs directory=my_dir dumpfile=expdp.dmp logfile=expdp.log

##导出多个用户,例:
expdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=expdp.log schemas=(eprkjs,eprk)

##导出整个数据库(sys、ordsys、mdsys的用户数据不会被导出)例:
expdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=expdp.log full=y

expdp参数说明:

table_exists_action
说明: 追加数据

remap_schema=用户1:用户2;
示例:remap_schema=eprk:eprkcsg;
说明:变更表的所有者owner,即将数据的schema从用户1转换为用户2

remap_tablespace=表空间A:表空间B        
示例:remap_tablespace=EPRKDATA:EPRKINDEX;
说明:变更表空间的所有者owner,即将数据的tablespace 从表空间A转换为表空间B

attach=[schema_name.]job_name
示例: attach 任务名
说明:nodefault。连接到作业,进入交互模式。

导出模式,以下五个参数互斥。
full=[y|n]
说明:nodefault。导出所有数据和元数据。要执行完全导出,需要具有datapump_exp_full_database角色。

schemas=schema_name[,...]
说明:default current user's schema。导出用户。

tables=[schema_name.]table_name[:partition_name][,...]
说明:nodefault。导出表。

tablespaces=tablespace_name[,...]
示例:tablespaces=temp,example;
说明:nodefault。导出表空间。

transport_tablespaces=tablespace_name[,...]
说明:nodefault。导出可移动表空间。

过滤条件,以下三个参数互斥:
query=[schema.][table_name:] query_clause
示例:query='WHERE deptno=20';
说明:nodefault。按查询条件导出。

exclude=object_type[:name_clause][,...]
说明:nodefault。排除特定的对象类型。

include=object_type[:name_clause][,...]
说明:nodefault。包括特定的对象类型。

其他参数:
directory=directory_object
说明:default:data_pump_dir。导出路径。

dumpfile=[directory_object:]file_name[,...]
说明:default:expdat.dmp。导出的文件名。

logfile=[directory_object:]file_name
说明:default:export.log。导出的日志文件名。

content=[all|data_only|metadata_only]
说明:default:all。指定要导出的数据。

parallel=integer
示例: parallel=4
说明:default:1。并行度,该值应小于等于dmp文件数量,或可以为'dumpfile='使用替换变量'%U'。
RAC环境中,并行度大于1时,注意目录应该为共享目录。

compression=[all|data_only|metadata_only|none]
说明:default:metadata_only。压缩。

parfile=[directory_path]file_name
说明:nodefault。指定导出参数文件名称。

network_link=source_database_link
说明:nodefault。连接到源数据库进行导出。

filesize=integer[b|kb|mb|gb|tb]
说明:default:0不限制大小。指定每个dmp文件的最大大小。
如果此参数小于将要导出的数据大小,将报错ORA-39095。

job_name=jobname_string
说明:default:system-generated name of the form SYS_EXPORT_<mode>_NN。指定job名称。

version=[compatilble|latest|version_string]
说明:default:compatible。默认兼容模式,可以指定导出dmp文件的版本。

IMPDP导入

注意:
1、expdp导出的文件不能使用imp导入,只能通过impdp导入数据库。
2、导入时遇到已存在的对象,默认会跳过这个对象,继续导入其他对象。
3、导入时应确认dmp文件和目标数据库的tablespace、schema是否对应。
4、导入dmp文件时,应确定dmp文件导出时的命令,以便顺利导入数据。

拿到一个dmp文件,如果忘记了导出命令,可以通过以下方法确认(非官方,生产数据勿使用):
确认dmp文件是exp导出还是expdp导出 1)xxd test.dmp | more

expdp导出的文件开头为0301,exp导出的文件开头为0303

2)strings test.dmp | more

expdp导出的dmp文件头信息:
"SYS"."SYS_EXPORT_TABLE_01" -----job名称
x86_64/Linux 2.4.xx -----操作系统版本
bjdb -----数据库名称
ZHS16GBK -----数据库字符集
11.02.00.04.00 -----数据库版本

exp导出的dmp文件头信息:
iEXPORT:V11.02.00 -----版本
USCOTT -----用户
RTABLES -----对象

确认expdp导出的dmp文件的导出命令
strings test.dmp | grep CLIENT_COMMAND

impdp命令示例

##导入dmp文件中的所有数据,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log full=y

##导入一张表,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log tables=eprkjs.eprk_usercache

##导入多张表,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log tables=(eprkjs.eprk_usercache,eprkjs.sm_user)

##导入一个用户,例:
impdp eprkjs/ep81274 schemas=eprkjs directory=my_dir dumpfile=expdp.dmp logfile=impdp.log 

##导入多个用户,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log schemas=(eprkjs,eprk)

以导入dmp文件中的所有数据为例

##并行导入:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp%U.dmp logfile=impdp.log parallel=5

##导入元数据(包含表定义、存储过程、函数等等):
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log content=metadata_only

##导入存储过程,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log include=procedure

##导入函数和视图,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log include=(function,view)

##导入数据,但不包括索引,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log exclude=index

##重命名表名导入,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log remap_table=eprkjs.emp:emp1

##重命名schema名导入,例:  
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log remap_schema=eprkjs:eprk

##重命名表空间名导入,例:
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log remap_tablespace=EPRKDATA:EPRKINDEX

##导入时,忽略所有对象的段属性,这样导入时对象都创建在目标数据库用户默认的表空间上。
impdp eprkjs/ep81274 directory=my_dir dumpfile=expdp.dmp logfile=impdp.log transform=segment_attributes:n

##将dmp文件的ddl语句导入到一个文件,不导入数据库,例:
impdp system/oracle directory=my_dir dumpfile=expdp.dmp logfile=impdp.log sqlfile=import.sql

impdp参数说明

attach=[schema_name.]job_name
说明:nodefault。连接到作业,进入交互模式。

导入模式,以下五个参数互斥。
full=[Y|N]
说明:default:yes。导入dmp文件的所有数据和元数据。

schemas=schema_name[,...]
说明:nodefault。导入用户。

tables=[schema_name.]table_name[:partition_name][,...]
说明:nodefault。导入表。

tablespaces=tablespace_name[,...]
说明:nodefault。导入表空间。

transport_tablespaces=tablespace_name[,...]
说明:nodefault。导入可移动表空间。

过滤条件,以下三个参数互斥:
query=[schema.][table_name:] query_clause
说明:nodefault。按查询条件导入。

exclude=object_type[:name_clause][,...]
说明:nodefault。排除特定的对象类型。

include=object_type[:name_clause][,...]
说明:nodefault。包括特定的对象类型。

其他参数:
directory=directory_object
说明:default:data_pump_dir。导入路径。

dumpfile=[directory_object:]file_name[,...]
说明:default:expdat.dmp。导入的文件名。

logfile=[directory_object:]file_name
说明:default:export.log。导入的日志文件名。

content=[all|data_only|metadata_only]
说明:default:all。指定要导入的数据。

parallel=integer
说明:default:1。并行度,该值应小于等于dmp文件数量,或可以为'dumpfile='使用替换变量'%U'。

compression=[all|data_only|metadata_only|none]
说明:default:metadata_only。压缩。

parfile=[directory_path]file_name
说明:nodefault。指定导入参数文件名称。

network_link=source_database_link
说明:nodefault。连接到源数据库进行导入。

job_name=jobname_string
说明:default:system-generated name of the form SYS_EXPORT_<mode>_NN。指定job名称。

version=[compatilble|latest|version_string]
说明:default:compatible。默认兼容模式,可以指定导入dmp文件的版本。

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
说明:nodefault。允许导入期间重命名表名。

REMAP_SCHEMA=source_schema:target_schema
说明:nodefault。允许导入期间重命名schema名。

REMAP_TABLESPACE=source_tablespace:target_tablespace
说明:nodefault。允许导入期间重命名表空间名。

TRANSFORM = transform_name:value[:object_type]
说明:nodefault。允许改正正在导入的对象的DDL。

SQLFILE=[directory_object:]file_name
说明:nodefault。根据其他参数,将所有的 SQL DDL 写入指定的文件。

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
说明:default:skip(if content=data_only is specified,then the default is append)

交互模式

进入交互可以操作导入导出作业。
进入交互模式的方法:
1、导入导出命令行执行期间按Ctrl + c
2、expdp attach=jobname或impdp attach=jobnam 查看导入导出日志可以看到jobname,也可以通过查询dba_datapump_jobs找到jobname。

赞(0) 打赏
未经允许不得转载:刘鹏博客 » Oracle学习笔记–数据泵
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏