数据泵作为oracle最基础的运维工具，对于老司机来说，也许不是什么新鲜的物件，但是对于刚接触oracle运维不久，尤其是从事开发的同事，对数据泵则比较陌生。这里根据自己对该工具的认识和使用习惯，做一个用法汇总说明，留备日后查用。 1.获取使用帮助expdp/impdp -help expdp -help Export: Release 11.2.0.4.0 - Production on Mon Dec 20 17:47:40 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. The Data Pump export utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command: Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp You can control how Export runs by entering the 'expdp' command followed by various parameters. To specify parameters, you use keywords: Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. ------------------------------------------------------------------------------ The available keywords and their descriptions follow. Default values are listed within square brackets. ATTACH Attach to an existing job. For example, ATTACH=job_name. CLUSTER Utilize cluster resources and distribute workers across the Oracle RAC. Valid keyword values are: [Y] and N. COMPRESSION Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE. CONTENT Specifies data to unload. Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY. DATA_OPTIONS Data layer option flags. Valid keyword values are: XML_CLOBS. DIRECTORY Directory object to be used for dump and log files. DUMPFILE Specify list of destination dump file names [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp. ENCRYPTION Encrypt part or all of a dump file. Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE. ENCRYPTION_ALGORITHM Specify how encryption should be done. Valid keyword values are: [AES128], AES192 and AES256. ENCRYPTION_MODE Method of generating encryption key. Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT]. ENCRYPTION_PASSWORD Password key for creating encrypted data within a dump file. ESTIMATE Calculate job estimates. Valid keyword values are: [BLOCKS] and STATISTICS. ESTIMATE_ONLY Calculate job estimates without performing the export. EXCLUDE Exclude specific object types. For example, EXCLUDE=SCHEMA:"='HR'". FILESIZE Specify the size of each dump file in units of bytes. FLASHBACK_SCN SCN used to reset session snapshot. FLASHBACK_TIME Time used to find the closest corresponding SCN value. FULL Export entire database [N]. HELP Display Help messages [N]. INCLUDE Include specific object types. For example, INCLUDE=TABLE_DATA. JOB_NAME Name of export job to create. LOGFILE Specify log file name [export.log]. NETWORK_LINK Name of remote database link to the source system. NOLOGFILE Do not write log file [N]. PARALLEL Change the number of active workers for current job. PARFILE Specify parameter file name. QUERY Predicate clause used to export a subset of a table. For example, QUERY=employees:"WHERE department_id > 10". REMAP_DATA Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO. REUSE_DUMPFILES Overwrite destination dump file if it exists [N]. SAMPLE Percentage of data to be exported. SCHEMAS List of schemas to export [login schema]. SERVICE_NAME Name of an active Service and associated resource group to constrain Oracle RAC resources. SOURCE_EDITION Edition to be used for extracting metadata. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. TABLES Identifies a list of tables to export. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995. TABLESPACES Identifies a list of tablespaces to export. TRANSPORTABLE Specify whether transportable method can be used. Valid keyword values are: ALWAYS and [NEVER]. TRANSPORT_FULL_CHECK Verify storage segments of all tables [N]. TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded. VERSION Version of objects to export. Valid keyword values are: [COMPATIBLE], LATEST or any valid database version. ------------------------------------------------------------------------------ The following commands are valid while in interactive mode. Note: abbreviations are allowed. ADD_FILE Add dumpfile to dumpfile set. CONTINUE_CLIENT Return to logging mode. Job will be restarted if idle. EXIT_CLIENT Quit client session and leave job running. FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands. HELP Summarize interactive commands. KILL_JOB Detach and delete job. PARALLEL Change the number of active workers for current job. REUSE_DUMPFILES Overwrite destination dump file if it exists [N]. START_JOB Start or resume current job. Valid keyword values are: SKIP_CURRENT. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. STOP_JOB Orderly shutdown of job execution and exits the client. Valid keyword values are: IMMEDIATE. impdp -help Import: Release 11.2.0.4.0 - Production on Mon Dec 20 17:48:46 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. The Data Pump Import utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command: Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp You can control how Import runs by entering the 'impdp' command followed by various parameters. To specify parameters, you use keywords: Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp USERID must be the first parameter on the command line. ------------------------------------------------------------------------------ The available keywords and their descriptions follow. Default values are listed within square brackets. ATTACH Attach to an existing job. For example, ATTACH=job_name. CLUSTER Utilize cluster resources and distribute workers across the Oracle RAC. Valid keyword values are: [Y] and N. CONTENT Specifies data to load. Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY. DATA_OPTIONS Data layer option flags. Valid keywords are: SKIP_CONSTRAINT_ERRORS. DIRECTORY Directory object to be used for dump, log and SQL files. DUMPFILE List of dump files to import from [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp. ENCRYPTION_PASSWORD Password key for accessing encrypted data within a dump file. Not valid for network import jobs. ESTIMATE Calculate job estimates. Valid keywords are: [BLOCKS] and STATISTICS. EXCLUDE Exclude specific object types. For example, EXCLUDE=SCHEMA:"='HR'". FLASHBACK_SCN SCN used to reset session snapshot. FLASHBACK_TIME Time used to find the closest corresponding SCN value. FULL Import everything from source [Y]. HELP Display help messages [N]. INCLUDE Include specific object types. For example, INCLUDE=TABLE_DATA. JOB_NAME Name of import job to create. LOGFILE Log file name [import.log]. NETWORK_LINK Name of remote database link to the source system. NOLOGFILE Do not write log file [N]. PARALLEL Change the number of active workers for current job. PARFILE Specify parameter file. PARTITION_OPTIONS Specify how partitions should be transformed. Valid keywords are: DEPARTITION, MERGE and [NONE]. QUERY Predicate clause used to import a subset of a table. For example, QUERY=employees:"WHERE department_id > 10". REMAP_DATA Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO. REMAP_DATAFILE Redefine data file references in all DDL statements. REMAP_SCHEMA Objects from one schema are loaded into another schema. REMAP_TABLE Table names are remapped to another table. For example, REMAP_TABLE=HR.EMPLOYEES:EMPS. REMAP_TABLESPACE Tablespace objects are remapped to another tablespace. REUSE_DATAFILES Tablespace will be initialized if it already exists [N]. SCHEMAS List of schemas to import. SERVICE_NAME Name of an active Service and associated resource group to constrain Oracle RAC resources. SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state. SOURCE_EDITION Edition to be used for extracting metadata. SQLFILE Write all the SQL DDL to a specified file. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. STREAMS_CONFIGURATION Enable the loading of Streams metadata TABLE_EXISTS_ACTION Action to take if imported object already exists. Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE. TABLES Identifies a list of tables to import. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995. TABLESPACES Identifies a list of tablespaces to import. TARGET_EDITION Edition to be used for loading metadata. TRANSFORM Metadata transform to apply to applicable objects. Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE. TRANSPORTABLE Options for choosing transportable data movement. Valid keywords are: ALWAYS and [NEVER]. Only valid in NETWORK_LINK mode import operations. TRANSPORT_DATAFILES List of data files to be imported by transportable mode. TRANSPORT_FULL_CHECK Verify storage segments of all tables [N]. TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded. Only valid in NETWORK_LINK mode import operations. VERSION Version of objects to import. Valid keywords are: [COMPATIBLE], LATEST or any valid database version. Only valid for NETWORK_LINK and SQLFILE. ------------------------------------------------------------------------------ The following commands are valid while in interactive mode. Note: abbreviations are allowed. CONTINUE_CLIENT Return to logging mode. Job will be restarted if idle. EXIT_CLIENT Quit client session and leave job running. HELP Summarize interactive commands. KILL_JOB Detach and delete job. PARALLEL Change the number of active workers for current job. START_JOB Start or resume current job. Valid keywords are: SKIP_CURRENT. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. STOP_JOB Orderly shutdown of job execution and exits the client. Valid keywords are: IMMEDIATE. 2.使用数据泵的前提条件 --创建备份路径别名（参数directory的值） create directory zhul as 'backup_path'; --授予特定用户对备份路径别名的使用权限 grant read,write on directory zhul to &username; 3.常用导出命令举例 --按表模式导出(数据库本地可不使用服务名) expdp zhul/oracle@orcl tables=testtab1.testtab2 dumpfile =expdp_test2tab_20211222.dmp logfile=expdp_test2tab_20211222.log directory=zhul job_name=my_expdp --按查询条件导出(数据库本地可不使用服务名) expdp zhul/oracle@orcl tables=testtab1 dumpfile =expdp_testtab1_sub_20211222.dmp logfile=expdp_testtab1_sub_20211222.log directory=zhul job_name=my_expdp query='"where rownum<11"' --按表空间导出(数据库本地可不使用服务名) expdp zhul/oracle@orcl dumpfile=expdp_tablespace_name_20211222.dmp tablespaces=ZHUL logfile=expdp_tablespace_name_20211222.log directory=zhul job_name=my_expdp --按用户导出(可以使用parallel加速导出，注意需要用户表的数量大于并行度，否则无意义) expdp zhul/oracle DIRECTORY=zhul DUMPFILE=expdp_zhul_20211222_U%.dmp SCHEMAS=zhul logfile=expdp_zhul_20211222.log parallel=4 job_name=my_expdp --全库导出 expdp \"/as sysdba\" DIRECTORY=zhul DUMPFILE=expdp_full_20211222_U%.dmp full=y logfile=expdp_full_20211222.log parallel=4 expdp system/password DIRECTORY=zhul DUMPFILE=expdp_full_20211222_U%.dmp full=y logfile=expdp_full_20211222.log parallel=4 --数据泵导出的一个parfile例子 DIRECTORY=zhul DUMPFILE=expdp_orcl_20211222_%U.dmp exclude=table:"like 'ADP%'",table:"in ('ADP_T8_C','INF_GM_ES','OCP_PENDING_MO')" include=SCHEMA:"IN('CUSTOMER','OPCO','ADPL','ADPN','ADPV','GIOON')",role parallel=4 CLUSTER=NO LOGFILE=expdp_orcl_20211222.log 4.常用导入命令举例 --按表模式导入(数据库本地可不使用服务名) impdp zhul/oracle@orcl dumpfile =expdp_test2tab_20211222.dmp logfile=impdp_test2tab_20211222.log directory=zhul job_name=my_impdp --按查询条件导出的导入(数据库本地可不使用服务名) impdp zhul/oracle@orcl dumpfile =impdp_testtab1_sub_20211222.dmp logfile=impdp_testtab1_sub_20211222.log directory=zhul job_name=my_impdp --按表空间导出(数据库本地可不使用服务名，注意表空间及相应的用户提前建立) impdp zhul/oracle@orcl dumpfile=expdp_tablespace_name_20211222.dmp logfile=impdp_tablespace_name_20211222.log directory=zhul job_name=my_impdp --按用户导出的导入(可以使用parallel加速导入，注意需要用户表的数量大于并行度，否则无意义) impdp zhul/oracle DIRECTORY=zhul DUMPFILE=expdp_zhul_20211222_U%.dmp SCHEMAS=zhul logfile=impdp_zhul_20211222.log parallel=4 job_name=my_impdp --全库导出 impdp \"/as sysdba\" DIRECTORY=zhul DUMPFILE=expdp_full_20211222_U%.dmp full=y logfile=impdp_full_20211222.log parallel=4 impdp system/password DIRECTORY=zhul DUMPFILE=expdp_full_20211222_U%.dmp full=y logfile=impdp_full_20211222.log parallel=4 5.注意事项 如果备份整个数据库，操作用户需要具有exp_full_database权限，建议使用sys/system操作，业务用户只需要有对备份路径别名的读写权限即可备份所管理的对象如表、视图、函数等；另外，备份前，如果回收站被打开，建议清理回收站，避免备份垃圾数据而消耗空间和时间。 备份空间估算，需要注意备份文件系统存储空间是否能容纳备份集，如果文件系统空间不允许，可以使用压缩参数compression=all执行压缩备份，压缩比例大约是5：1，可以使用ESTIMATE_ONLY进行评估，但是评估结果只能作为参考并不准确。 在使用impdp导入时，建议提前创建源库对等的表空间和用户，原则上数据泵会自动创建导出源端库的表空间和用户，但是需要单机单实例对应的数据文件存放的文件系统完全一致、集群rac实例对应的磁盘组名称完全一致，并且文件系统或者rac实例对应的磁盘组原则上不能小于源端库（部分导出恢复除外）。 如果是从rac集群导出，要使用cluster=no参数保证备份任务始终在一个节点运行，否则容易出现备份进程分配到其他节点导致备份失败。 停止数据泵作业，通过ctrl+c并不能停止数据泵备份作业，需要使用数据泵kill_job执行备份作业终止： --使用导入命令 impdp \"/as sysdba\" DIRECTORY=zhul DUMPFILE=expdp_full_20211222_U%.dmp full=y logfile=impdp_full_20211222.log parallel=4 job_name=my_expdp --终止过程 impdp \"/as sysdba\" attach=my_expdp 让后执行kill_job 如kill_job命令无法停止备份作业，那么可以通过ps -ef|grep expdp或者ps -ef|grep impdp查找要终止的备份或者恢复作业，然后通过kill -9 prosscess_num的方式终止备份作业。但是，需要注意的是，这样可能导致数据泵备份作业残留，引起后续备份或者导入作业无法正常运行，需要清理oracle数据库的内部备份作业表，甚至有时候需要重启dm00进程。 SELECT 'drop table '|| o.owner||'.'||object_name ||' purge ;' FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%'; 如果导入时要导入到不同名用户或者导入到别的表空间，需要借助REMAP_TABLESPACE、REMAP_SCHEMA两个参数。 6.贡献十个处理过的数据泵相关问题 ORACLE for aix 11.2.0.1 DATAPUMP expdp之BUG 9470768 ORACLE 数据泵expdp莫名其妙的报错ORA-31693&ORA-19502&ORA-27063 oracle expdp作业外表报错ORA-20011&KUP-11024&ORA-29913 ORACLE impdp或expdp与ORA-31693&ORA-31640&ORA-19505&ORA-27037 ORACLE expdp备份到windows网络共享文件目录（NFS） ORACLE expdp导出远程库指定用户数据到本地数据库 ORACLE expdp备份与ORA-31693、ORA-02354、ORA-01555 ORACLE expdp备份与ORA-31693、ORA-02354、ORA-02149 ORACLE的EXPDP与ORA-31626、ORA-31637、ORA-06512、ORA-31635 ORACLE的EXPDP与ORA-39125、ORA-01555、ORA-06512
ORACLE 数据泵用法汇总说明#2021年底大盘点#
