绝对干货!|Oracle 数据泵命令全解析
引言
Oracle数据库之间迁移数据可以采用的最有效的工具之一就是
数据泵(expdp/impdp)。
但是expdp和impdp只能在数据库服务端使用,不能在客户端使用。
笔者结合自身经验总结了expdp和impdp常用到的命令,部分信息参考Oracle官方文档。
一、directory:
select * from dba_directories;create directory mydata as '/home/oracle';grant read,write on directory mydata to username;
二、expdp命令解析
1、导数的数据库用户需要拥有对directory_object的读写权限。
2、操作系统中需要已经存在directory_object指定的路径。
3、oracle用户拥有对directory_object指定路径的读写权限。
4、system用户导出用户,会将创建用户和授予系统权限的元数据也导出,普通用户不能导出这些元数据。
expdp命令示例
导出一张表:
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log tables=tablename
导出多张表:
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log tables=tablename1,tablename2
导出一个用户:
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log schemas=username
导出多个用户:
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log schemas=username1,username2
以下以导出一个用户为例
并行导出:
- expdp username/passwd directory=mydata dumpfile=expdp%U.dmp logfile=expdp.log schemas=username parallel=8
导出用户元数据(包含表定义、存储过程、函数等):
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log schemas=username content=metadata_only
导出用户存储过程:
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log schemas=username include=procedure
导出用户函数和视图:
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log schemas=scott include=function,view
导出一个用户,但不包括索引:
- expdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=expdp.log schemas=scott exclude=index
expdp参数说明:
连接到作业,进入交互模式。
- attach=[schema_name.]job_name
以下五个参数互斥
导出所有数据和元数据。
- full=[yes|no]
导出用户。
- schemas=schema_name[…]
导出表。
- tables=[schema_name.]table_name[:partition_name][,…]
导出表空间。
- tablespaces=tablespace_name[,…]
导出可移动表空间。
- transport_tablespaces=tablespace_name[,…]
以下三个条件互斥
按查询条件导出。
- query=[schema.][table_name:] query_clause
排除特定的对象类型。
- exclude=object_type[:name_clause][,…]
包括特定的对象类型。
- include=object_type[:name_clause][,…]
其他参数:
导出路径。
- directory=directory_object
expdat.dmp。导出的文件名。
- dumpfile=[directory_object:]file_name[,…]
export.log。导出的日志文件名。
- logfile=[directory_object:]file_name
指定要导出的数据,默认all。
- content=[all|data_only|metadata_only]
并行度,默认1,该值应小于等于dmp文件数量
- parallel=integer
压缩,默认metadata_only
- compression=[all|data_only|metadata_only|none]
指定导出参数文件名称。
- parfile=[directory_path]file_name
连接到源数据库进行导出。
- network_link=source_database_link
指定每个dmp文件的最大大小,默认0不限制大小。
如果此参数小于将要导出的数据大小,将报错ORA-39095。
- filesize=integer[b|kb|mb|gb|tb]
指定job名称。
- job_name=jobname
默认兼容模式,可以指定导出dmp文件的版本。
- version=[compatilble|latest|version_string]
三、impdp命令解析
1、expdp导出的文件不能使用imp导入,只能通过impdp导入数据库。
2、导入时遇到已存在的对象,默认会跳过这个对象,继续导入其他对象。
3、导入时应确认dmp文件和目标数据库的tablespace、schema是否对应。
4、导入dmp文件时,应确定dmp文件导出时的命令,以便顺利导入数据。
- impdp命令示例
导入dmp文件中的所有数据:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log full=y
导入一张表,例:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log tables=tablename
导入多张表,例:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log tables=tablename1,tablename2
导入一个用户,例:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log schemas=username
导入多个用户,例:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log schemas=username1,username2
以导入dmp文件中的所有数据为例
并行导入:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log parallel=8
导入元数据(包含表定义、存储过程、函数等等):
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log content=metadata_only
导入存储过程:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log include=procedure
导入函数和视图:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log include=function,view
导入数据,但不包括索引:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log exclude=index
重命名表名导入:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log remap_table=tablename1:tablename2
重命名schema名导入:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log remap_schema=username1:username2
重命名表空间名导入:
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log remap_tablespace=tablespacename1:tablespacename2
导入时,忽略所有对象的段属性,这样导入时对象都创建在目标数据库用户默认的表空间上。
- impdp username/passwd directory=mydata dumpfile=expdp.dmp logfile=impdp.log transform=segment_attributes:n
impdp参数说明
连接到作业,进入交互模式。
- attach=[schema_name.]job_name
以下五个参数互斥
导入dmp文件的所有数据和元数据,默认ye
- full=[yes|no]
导入用户。
- schemas=schema_name[,…]
导入表。
- tables=[schema_name.]table_name[:partition_name][,…]
导入表空间。
- tablespaces=tablespace_name[,…]
导入可移动表空间。
- transport_tablespaces=tablespace_name[,…]
以下三个参数互斥:
按查询条件导入。
- query=[schema.][table_name:] query_clause
排除特定的对象类型。
- exclude=object_type[:name_clause][,…]
包括特定的对象类型。
- include=object_type[:name_clause][,…]
其他参数:
导入路径
- directory=directory_object
导入的文件名。
- dumpfile=[directory_object:]file_name[,…]
导入的日志文件名。
- logfile=[directory_object:]file_name
指定要导入的数据,默认all
- content=[all|data_only|metadata_only]
并行度,该值应小于等于dmp文件数量,默认1
- parallel=integer
压缩,默认metadata_only
- compression=[all|data_only|metadata_only|none]
指定导入参数文件名称。
- parfile=[directory_path]file_name
连接到源数据库进行导入。
- network_link=source_database_link
指定job名称。
- job_name=jobname
默认兼容模式,可以指定导入dmp文件的版本。
- version=[compatilble|latest|version_string]
允许导入期间重命名表名。
- REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
允许导入期间重命名schema名
- REMAP_SCHEMA=source_schema:target_schema
允许导入期间重命名表空间名。
- REMAP_TABLESPACE=source_tablespace:target_tablespace
默认skip
- TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
四、部分报错总结
1、系统目录未建立报错:
ORA-39002: invalid operationORA-39070: Unable to open the log file.ORA-29283: invalid file operationORA-06512: at "SYS.UTL_FILE", line 536ORA-29283: invalid file operation
2、impdp导入exp导出的dmp文件,报错:
ORA-39000: bad dump file specificationORA-39143: dump file "/home/oracle/expdp.dmp" may be an original export dump file
3、如果导出的数据库版本比导入的数据版本高,需要在导出时加上参数version=要导入的数据库版本。否则报错:
ORA-39001: invalid argument valueORA-39000: bad dump file specificationORA-31640: unable to open dump file "/home/oracle/expdp.dmp" for readORA-27037: unable to obtain file status