|
Oracle数据泵 一、数据泵的作用： 1.实现逻辑备份和逻辑恢复 2.在数据库用户之间移动对象 3.在数据库之间移动对象 4.实现表空间转移 二 、数据泵的特点与传统导出导入的区别 1.EXP和IMP是客户段工具程序， EXPDP和IMPDP是服务端的工具程序 2.EXP和IMP效率比较低. EXPDP和IMPDP效率高 3.数据泵功能强大并行、过滤、转换、压缩、加密、交互等等 4.数据泵不支持9i以前版本， EXP/IMP短期内还是比较适用 5.同exp/imp数据泵导出包括导出表,导出方案,导出表空间,导出数据库4种方式. 三、DATA PUMP的体系结构：
四、EXPDP语法介绍： 数据泵导出实用程序提供了一种用于在 Oracle 数据库之间传输数据对象的机制。该实用程序可以使用以下命令进行调用: 示例: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp 您可以控制导出的运行方式。具体方法是: 在 'expdp' 命令后输入各种参数。要指定各参数，请使用关键字: 格式: expdp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN) 示例: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott 或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表 USERID 必须是命令行中的第一个参数。 关键字说明 (默认) ------------------------------------------------------------------------------ ATTACH 连接到现有作业, 例如 ATTACH [=作业名]。 COMPRESSION 减小有效的转储文件内容的大小 关键字值为: (METADATA_ONLY) 和 NONE。 CONTENT 指定要卸载的数据，其中有效关键字为: (ALL), DATA_ONLY 和 METADATA_ONLY。 DIRECTORY 供转储文件和日志文件使用的目录对象。 DUMPFILE 目标转储文件 (expdat.dmp) 的列表, 例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。 ENCRYPTION_PASSWORD 用于创建加密列数据的口令关键字。 ESTIMATE 计算作业估计值，其中有效关键字为: (BLOCKS) 和 STATISTICS。 ESTIMATE_ONLY 在不执行导出的情况下计算作业估计值。 EXCLUDE 排除特定的对象类型, 例如 EXCLUDE=TABLE:EMP。 FILESIZE 以字节为单位指定每个转储文件的大小。 FLASHBACK_SCN 用于将会话快照设置回以前状态的 SCN。 FLASHBACK_TIME用于获取最接近指定时间的 SCN 的时间。 FULL 导出整个数据库 (N)。 HELP 显示帮助消息 (N)。 INCLUDE 包括特定的对象类型, 例如 INCLUDE=TABLE_DATA。 JOB_NAME 要创建的导出作业的名称。 LOGFILE 日志文件名 (export.log)。 NETWORK_LINK 链接到源系统的远程数据库的名称。 NOLOGFILE 不写入日志文件 (N)。 PARALLEL 更改当前作业的活动 worker 的数目。 PARFILE 指定参数文件。 QUERY 用于导出表的子集的谓词子句。 SAMPLE 要导出的数据的百分比; SCHEMAS 要导出的方案的列表 (登录方案)。 STATUS 在默认值 (0) 将显示可用时的新状态的情况下, 要监视的频率 (以秒计) 作业状态。 TABLES 标识要导出的表的列表 - 只有一个方案。 TABLESPACES 标识要导出的表空间的列表。 TRANSPORT_FULL_CHECK 验证所有表的存储段 (N)。 TRANSPORT_TABLESPACES 要从中卸载元数据的表空间的列表。 VERSION 要导出的对象的版本, 其中有效关键字为: (COMPATIBLE), LATEST 或任何有效的数据库版本。 下列命令在交互模式下有效。 注: 允许使用缩写 命令 说明 ------------------------------------------------------------------------------ ADD_FILE 向转储文件集中添加转储文件。 CONTINUE_CLIENT返回到记录模式。如果处于空闲状态, 将重新启动作业。 EXIT_CLIENT 退出客户机会话并使作业处于运行状态。 FILESIZE 后续 ADD_FILE 命令的默认文件大小 (字节)。 HELP 总结交互命令。 KILL_JOB 分离和删除作业。 PARALLEL 更改当前作业的活动 worker 的数目。 PARALLEL=<worker 的数目>。 START_JOB 启动/恢复当前作业。 STATUS 在默认值 (0) 将显示可用时的新状态的情况下, 要监视的频率 (以秒计) 作业状态。 STATUS[=interval] STOP_JOB 顺序关闭执行的作业并退出客户机。 STOP_JOB=IMMEDIATE 将立即关闭 数据泵作业。 五、IMPDP语法介绍： 数据泵导入实用程序提供了一种用于在 Oracle 数据库之间传输 数据对象的机制。该实用程序可以使用以下命令进行调用: 示例: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp 您可以控制导入的运行方式。具体方法是: 在 'impdp' 命令后输入 各种参数。要指定各参数, 请使用关键字: 格式: impdp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN) 示例: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp USERID 必须是命令行中的第一个参数。 关键字 说明 (默认) ------------------------------------------------------------------------------ ATTACH 连接到现有作业, 例如 ATTACH [=作业名]。 CONTENT 指定要加载的数据, 其中有效关键字为：(ALL), DATA_ONLY 和 METADATA_ONLY。 DIRECTORY 供转储文件, 日志文件和 sql 文件使用的目录对象。 DUMPFILE 要从 (expdat.dmp) 中导入的转储文件的列表, 例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。 ENCRYPTION_PASSWORD 用于访问加密列数据的口令关键字。 此参数对网络导入作业无效。 ESTIMATE 计算作业估计值, 其中有效关键字为: (BLOCKS) 和 STATISTICS。 EXCLUDE 排除特定的对象类型, 例如 EXCLUDE=TABLE:EMP。 FLASHBACK_SCN 用于将会话快照设置回以前状态的 SCN。 FLASHBACK_TIME用于获取最接近指定时间的 SCN 的时间。 FULL 从源导入全部对象 (Y)。 HELP 显示帮助消息 (N)。 INCLUDE 包括特定的对象类型, 例如 INCLUDE=TABLE_DATA。 JOB_NAME 要创建的导入作业的名称。 LOGFILE 日志文件名 (import.log)。 NETWORK_LINK 链接到源系统的远程数据库的名称。 NOLOGFILE 不写入日志文件。 PARALLEL 更改当前作业的活动 worker 的数目。 PARFILE 指定参数文件。 QUERY 用于导入表的子集的谓词子句。 REMAP_DATAFILE在所有 DDL 语句中重新定义数据文件引用。 REMAP_SCHEMA 将一个方案中的对象加载到另一个方案。 REMAP_TABLESPACE 将表空间对象重新映射到另一个表空间。 REUSE_DATAFILES如果表空间已存在, 则将其初始化 (N)。 SCHEMAS 要导入的方案的列表。 SKIP_UNUSABLE_INDEXES 跳过设置为无用索引状态的索引。 SQLFILE 将所有的 SQL DDL 写入指定的文件。 STATUS 在默认值 (0) 将显示可用时的新状态的情况下, 要监视的频率 (以秒计) 作业状态。 STREAMS_CONFIGURATION 启用流元数据的加载 TABLE_EXISTS_ACTION 导入对象已存在时执行的操作。 有效关键字: (SKIP), APPEND, REPLACE 和 TRUNCATE。 TABLES 标识要导入的表的列表。 TABLESPACES 标识要导入的表空间的列表。 TRANSFORM 要应用于适用对象的元数据转换。 有效的转换关键字: SEGMENT_ATTRIBUTES, STORAGE OID 和 PCTSPACE。 TRANSPORT_DATAFILES 按可传输模式导入的数据文件的列表。 TRANSPORT_FULL_CHECK 验证所有表的存储段 (N)。 TRANSPORT_TABLESPACES 要从中加载元数据的表空间的列表。 仅在 NETWORK_LINK 模式导入操作中有效。 VERSION 要导出的对象的版本, 其中有效关键字为: (COMPATIBLE), LATEST 或任何有效的数据库版本。 仅对 NETWORK_LINK 和 SQLFILE 有效。 下列命令在交互模式下有效。 注: 允许使用缩写 命令 说明 (默认) ------------------------------------------------------------------------------ CONTINUE_CLIENT返回到记录模式。如果处于空闲状态, 将重新启动作业。 EXIT_CLIENT 退出客户机会话并使作业处于运行状态。 HELP 总结交互命令。 KILL_JOB 分离和删除作业。 PARALLEL 更改当前作业的活动 worker 的数目。 PARALLEL=<worker 的数目>。 START_JOB 启动/恢复当前作业。 START_JOB=SKIP_CURRENT 在开始作业之前将跳过 作业停止时执行的任意操作。 STATUS 在默认值 (0) 将显示可用时的新状态的情况下, 要监视的频率 (以秒计) 作业状态。 STATUS[=interval] STOP_JOB 顺序关闭执行的作业并退出客户机。 STOP_JOB=IMMEDIATE 将立即关闭数据泵作业。 六、实践操作： 1. 使用目录：DATA_PUMP_DIR [sql] SQL> select * from dba_directories; OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH ---------------------------------------------------------------- SYS ADMIN_DIR C:\ADE\aime_10.2_nt_push\oracle/md/admin SYS DATA_PUMP_DIR E:\admin\orcl\dpdump\ SYS WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/work SQL> grant read, write on directory data_pump_dir to scott; 授权成功。 2.EXPDP用法举例：
1)按用户导 expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1; 2)并行进程parallel expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3 3)按表名导 expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1; 4)按查询条件导 expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'; 5)按表空间导 expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example; 6)导整个数据库 expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y; 3.IMPDP用法举例：
1)导到指定用户下 impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott; 2)改变表的owner impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system; 3)导入表空间 impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example; 4)导入数据库 impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y; 5)追加数据 impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION 4. 案例：将数据库A中HR用户下所有对象导入到SCOTT用户下： 1）导出HR方案： [sql] expdp system/admin@orcl schemas=hr dumpfile=hr.dmp DIRECTORY=data_pump_dir nologfle=y; 2）将hr.dmp导入SCOTT方案： [sql] 启动 "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=hr.dmp directory=data_pump_dir remap_schema=hr:scott 处理对象类型 SCHEMA_EXPORT/USER ORA-31684: 对象类型 USER:"SCOTT" 已存在 处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT 处理对象类型 SCHEMA_EXPORT/ROLE_GRANT 处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE 处理对象类型 SCHEMA_EXPORT/TABLESPACE_QUOTA 处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA . . 导入了 "SCOTT"."COUNTRIES" 6.085 KB 25 行 . . 导入了 "SCOTT"."DEPARTMENTS" 6.632 KB 27 行 . . 导入了 "SCOTT"."EMPLOYEES" 15.76 KB 107 行 . . 导入了 "SCOTT"."JOBS" 6.609 KB 19 行 . . 导入了 "SCOTT"."JOB_HISTORY" 6.585 KB 10 行 . . 导入了 "SCOTT"."LOCATIONS" 7.710 KB 23 行 . . 导入了 "SCOTT"."REGIONS" 5.289 KB 4 行 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX 处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT 处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE 处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 处理对象类型 SCHEMA_EXPORT/TABLE/TRIGGER 处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 作业 "SYSTEM"."SYS_IMPORT_FULL_01" 已经完成, 但是有 1 个错误 (于 15:32:01 完成) 3）效果就是HR下面的对象都复制到了SCOTT用户下： [sql] SQL> select * from scott.employees where rownum<10; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL ----------- -------------------- ------------------------- ---------------- 100 Steven King SKING 101 Neena Kochhar NKOCHHAR 102 Lex De Haan LDEHAAN 103 Alexander Hunold AHUNOLD 104 Bruce Ernst BERNST 105 David Austin DAUSTIN 106 Valli Pataballa VPATABAL 107 Diana Lorentz DLORENTZ 108 Nancy Greenberg NGREENBE 已选择9行。 ORACLE expdp/impdp详解(原创)ORCALE10G提供了新的导入导出工具，数据泵。 Oracle官方对此的形容是：Oracle DataPump technology enables Very High-Speed movement of data and metadata from one database to another.其中Very High-Speed是亮点。 先说数据泵提供的主要特性（包括，但不限于）： 1. 支持并行处理导入、导出任务 2. 支持暂停和重启动导入、导出任务 3. 支持通过Database Link的方式导出或导入远端数据库中的对象 4. 支持在导入时通过Remap_schema、Remap_datafile、Remap_tablespace几个参数实现导入过程中自动修改对象属主、 数据文件或数据所在表空间。 5. 导入/导出时提供了非常细粒度的对象控制。通过Include、Exclude两个参数，甚至可以详细制定是否包含或不包含某个对象。 Warning: 1. 什么是Directory对象 Directory对象是Oracle10g版本提供的一个新功能。他是一个 指向，指向了操作系统中的一个路径。每个Directory都包含 Read,Write两个权限，可以通过Grant命令授权给指定的用户或角色。拥有读写权限的用户就可以读写该Directory对象指定的操作系统路 径下的文件。 2. 除了使用network_link参数意外，expdp生成的文件都是在服务器上(Directory指定的位置) 如何调用 1. 命令行方式 最简单的调用，但是写的参数有限，建议使用参数文件的方式。 2. 参数文件方式 最常用的方式。通常需要先编写一个参数文件。指定导出时需要的各种参数。然后以如下方式调用。 expdp user/pwd parfile=xxx.par 这个xxx.par即是我们编辑的参数文件。注意，在这个命令行后面，同样可以再跟别的参数， 甚至是在par参数文件中指定过的参数。如果执行命令中附加 的参数与参数文件中的参数有重复，最终采用哪个参数，会以参数最后出现的位置而定。如：expdp user/pwd parfile=xxx.par logfile=a.log，如果在参数文件中也指定了logfile，这里会以命令行中的logfile为准；如：expdp user/pwd logfile=a.log parfile=xxx.par，而这个，则会以参数文件中的为准，因为parfile=xxx.par写在命令行的后面。 3. 交互方式 Data Pump导入导出任务支持停止，重启等状态操作。如用户执行导入或者导出任务，执行了一半时，使用Crtl+C中断了任务（或其他原因导致的中断），此时 任务并不是被取消，而是被转移到后台。可以再次使用expdp/impdp命令，附加attach参数的方式重新连接到中断的任务中，并选择后续的操作。 这就是交互方式。 Warning : 什么是attach参数，每执行一个导入，或者导出，在命令的第一行，会有以下信息：Starting “BAM”.”SYS_EXPORT_SCHEMA_01″: bam/******** parfile=expdp_tbs.par，这个SYS_EXPORT_SCHEMA_01就是我们的attach参数。 -bash-3.00$ expdp bam/bam parfile=expdp_tbs.par Export: Release 10.2.0.4.0 – 64bit Production on Friday, 13 August, 2010 16:35:18 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting “BAM”.”SYS_EXPORT_SCHEMA_01″: bam/******** parfile=expdp_tbs.par 如果想使用交互方式，可以使用如：expdp attach SYS_EXPORT_SCHEMA_01 进入到交互模式 操作模式 1. 全库模式 导入或者导出整个数据库，对应impdp/expdp命令中的full参数，只有拥有dba或者 exp_full_database和imp_full_database权限的用户才能执行。 2. Schema模式 导出或导入Schema下的自有对象，对应impdp/expdp命令中的Schema参数，这是默认的操作模式。 如果拥有dba或者 exp_full_database和imp_full_database权限的用户执行的话，就可以导出或导入多个Schema中的对象。 3. 表模式 导出指定的表或者表分区（如果有分区的话）以及依赖该表的对象（如该表的索引，约束等，不过前提是这些对象在同一个 Schema中，或者执行的用户有相应的权限）。对应impdp/expdp命令中的Table参数。 4. 表空间模式 导出指定的表空间中的内容。对应impdp/expdp中的Tablespaces参数，这种模式类似于表模式和 Schema模式的补充。 5. 传输表空间模式 对应impdp/expdp中的Transport_tablespaces参数。这种模式与前面几种模式最显著的区 别是生成的Dump文件中并不包含具 体的逻辑数据，而只导出相关对象的元数据(即对象的定义，可以理解成表的创建语句)，逻辑数据仍然在表空间的数据文件中，导出时需要将元数据和数据文件同 时复制到目标端服务器。 这种导出方式效率很高，时间开销主要是花在复制数据文件产生的I/O上。expdp执行传输表空间模式的导出，用户必须 拥有 exp_full_database角色或者DBA角色。而通过传输表空间模式导入时，用户必须拥有imp_full_database角色或者DBA角 色。 过滤数据 过滤数据主要依赖于Query和Sample两个参数。其中Sample参数主要针对expdp导出功能。 1. Query 与exp命令中的Query功能类似，不过Expdp中，该参数功能得到了增强，控制的粒度更细。Expdp中的Query也是指定类 似where语句来限定记录。语法如下： Query = [Schema.][Table_name:] Query_clause 默认 情况如果不指定Schema.table_name,则Query_clause针对所有导出的表有效，或者你可以为每一个表指定不同的 Query_clause，如：导出a表中所有id<5的记录，导出b表中所有name=’a’的记录，则Query的参数应该如下： Query=A:”Where id<5″,B:”Where name=’a’” 如果Where条件前没有指定Schema名或者表名的话，默认就是针对当前所有要被 导出的表。如：Query=Where id <5 Warning: 建议把Query参数放入到参数文件中使用，以避免转义符带来的麻烦。 2. Sample 该参数用来指定导出数据的百分比，可指定的值的范围从0.000001到99.999999，语法如下： Sample=[[Schema_name.]Table_name:]sample_percent 指 定该参数以后，EXPDP导出将自动控制导出的记录量，如导出A表中50%的记录，设置的Sample参数如下： Sample=A:50 Warning: Sample_percent指定的值只是一个参考值，EXPDP会根据数据量算出一个近似值。 过滤对象 过滤对象主要依赖于Include和Exclude两个参数。这两个参数作用正好相反，在这两个参数中，可以指定你知道的任何对象 类型（如：Package、Procedure、Table等等）或者对象名称（支持通配符） 1. Exclude 反规则 指定不被包含的对象类型或者对象名称。指定了该参数以后，指定的对象类型对应的所有对象都不会被导入或导出。 如果被排除的对象有依赖的对象，那么其依赖的 对象也不会被导入或导出。如：通过Exclude参数指定不导出表对象的话，不仅指定的表不会被导出，连这些表关联的Index、Check等都不会被导出。Warning: 建议把Exclude参数放入到参数文件中使用，以避免转义符带来的麻烦。 2. Include 正规则 与Exclude正好相反。指定包含的对象类型或者对象名称。 Warning: 由于两个参数功能正好相反，因此在执行导入或导出命令时，两个参数不能同时使用，否则Oracle也不知道你想要干什么啦。 exclude/include参数用法： EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] -->排出特定对象 INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] -->包含特定对象 object_type子句用于指定对象的类型，如table,sequence,view,procedure,package等等 name_clause子句可以为SQL表达式用于过滤特定的对象名字。它由SQL操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象。 当未指定name_clause而仅仅指定object_type则所有该类型的对象都将被过滤或筛选。多个[object_type]:[name_clause]中间以逗号分割。 示例： expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')" impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'" 常用的过滤SQL表达式 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 system/manager parfile=exp.par 命令行下转义符的处理 Windows平台: D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\" 在Windows平台下，需要对象双引号进行转义，使用转义符\ Unix平台: 在未使用parfile文件的情形下，所有的符号都需要进行转义，包括括号，双引号，单引号等 % expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\" exclude/include常见的错误 任意需要转义的字符如果未转义或转义错误，都会产生ORA错误。下面给出几种常见的ORA错误。 ORA-39001: invalid argument value ORA-39071: Value for INCLUDE is badly formed. ORA-00936: missing expression ORA-39001: invalid argument value ORA-39071: Value for EXCLUDE is badly formed. ORA-00904: “DEPT”: invalid identifier ORA-39001: invalid argument value ORA-39041: Filter “INCLUDE” either identifies all object types or no object types. ORA-39001: invalid argument value ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types ORA-39001: invalid argument value ORA-39038: Object path “USER” is not supported for TABLE jobs. 高级过滤 在导出/导入的时候，我们常常有这样的需求，只想导出/导入表结构，或者只想导出/导入数据。幸运的是数据泵也提供了该功能。使用 Content参数。该参数有三个属性 1) ALL : 导出/导入对象定义和数据，该参数的默认值就是ALL 2) DATA_ONLY : 只导出/导入数据。 3) METADATA_ONLY : 只导出/导入对象定义。 Warning: 有一点值得注意的时，在执行导出的时候，如果使用了高级过滤，如只导出了数据，那么导入时，需要确保数据定义已经存在。否则数据都变成没有主子了。如果数 据定义已经存在，导入时最好指定data_only，否则会触发ORA-39151错误，因为对象已经存在了。 过滤已经存在的数据 我们知道，导入的表对象在目标库中已经存在，并且目标端没有创建数据完整性约束条件(RI)来检验数据的话，就有可能造 成数据被重复导入。数据泵提供了一 个新的参数Table_exists_action,可以一定程度上降低重复数据的产生。该参数用来控制如果要导入的表对象存在，执行什么操作。有以下几 个参数值： 1) SKIP : 跳过该表，继续处理下一个对象。该参数默认就是SKIP。值得注意的是，如果你同时指定了CONTENT参数为Data_only的话，SKIP参数无 效，默认为APPEND。 2) APPEND : 向现有的表中添加数据。 3) TRUNCATE : TRUNCATE当前表，然后再添加记录。使用这个参数需要谨慎，除非确认当前表中的数据确实无用。否则可能造成数据丢失。 4) REPLACE : 删除并重建表对象，然后再向其中添加数据。值得注意的是，如果同时指定了CONTENT参数为Data_only的话，REPLACE参数无效。 重定义表的Schema或表空间 我们还可能会遇到这样的需求，把A用户的对象转移到B用户，或者更换数据的表空间。数据泵通过 Remap_Schema和Remap_tablespace参数实现了该功能。 1) REMAP_SCHEMA : 重定义对象所属Schema 该参数的作用类似IMP中的Fromuser+Touser,支持多个Schema的转换，语法如下： REMAP_SCHEMA=Source_schema:Target_schema[,Source_schema:Target_schema] 如 把A的对象转换到C用户，将C转换到D用户。Remap_schema=a:b,c:d Warning: 不能在同一个IMPDP命令中指定remap_schema=a:b,a:c. 2) REMAP_TABLESPACE : 重定义对象所在的表空间。 该参数用来重映射导入对象存储的表空间，支持同时对多个表空间进行 转换，相互间用逗号分割。语法如下： REMAP_TABLESPACE=Source_tablespace:Target_tablespace[,Source_tablespace:Target_tablespace] Warning: 如果使用Remap_tablespace参数，则要保证导入的用户对目标表空间有读写权限。 优化导入/导出效率 对于大数据量来说，我们不得不考虑效率问题。数据泵对效率也提出了更高的要求。甚至官方的描述就是Oracle Data Pump technology enables Very High-Speed movement of data and metadata from one database to another.这里的Very High-Speed依赖我们的parallel参数。 所 有的优化操作都会有三种结果：变得更好、没有变化、变得更差。Parallel参数也是这样，并不是指定一个大于1的参数，性能就会有提升。 1) 对于导出的parallel 对于导出来说，由于dump文件只能由一个线程进行操作（包 括I/O处理），因此如果输出的DUMP文件只有一个，即使你指定再多的并行，实际工作仍然是 一个，而且还会触发ORA-39095错误。因此，建议设置该参数小于或等于生成的DUMP文件数量。那么，如何控制生成的DUMP文件数量呢？ EXPDP 命令提供了一个FILESIZE参数，用来指定单个DUMP文件的最大容量，要有效的利用parallel参数，filesize参数必不可少。 举 例：某用户对象占用了4G左右的空间，实际导出后的DUMP文件约为3G，我们尝试在导出该用户时指定并行度为4，设置单个文件不超过500M，则语法如 下： $ expdp user/pwd directory=dump_file dumpfile=expdp_20100820_%U.dmp logfile=expdp_20100820.log filesize=500M parallel=4 2) 对于导入的parallel 对于导入来说，使用parallel参数则要简单的多，我认为导入更能体现parallel参数的优势。 参数设置为几，则认为同时将几张表的内容导入到库中。 举例：某dmp文件中包含了200张表，我们尝试在导入该DMP文件时指定并行度为10，则 语法如下： $ impdp user/pwd directory=dump_file dumpfile=expdp_20100820.dmp logfile=impdp_20100820.log parallel=10 如何进入交互模式 在这里，我正在执行导入，我想进入交互模式，查看导入的状态。进入交互模式有两种方式，操作步骤如下： i 使用Ctrl+C退出当前模式 ii 在命令行模式下，执行Expdp/Impdp命令，同时指定attach参数连接到当前正在制定的导入/导出任务。如： expdp bam/bam attach=SYS_IMPORT_FULL_01 Warning:如果没有指定Attach参数，则默认进入当前正在运行的 任务。不过如果当前没有正在指定的任务，而且也没有给Attach赋值，那么就会报Ora-31626错误。 当命令行进入交互模式后，会显示如下 界面： Export> 7) 交互模式的操作 在交互模式中，支持下面几种操作。 i 查看JOB的运行状态 Export> status ii 回退到命令行 Export> continue_client iii 增加并行 Export> parallel=4 Warning: 在使用导出时，不能直接指定parallel参数，否则可能会遇到ORA-39095错误，因为如果要并行导出，则必须指定多个导出文件，这里的并行导出 是指，多个线程同时工作，同时从数据库中导出多个dmp文件来。 在Oracle Database Utilities中有如下解释： Because each active worker process or I/O server process writes exclusively to one file at a time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job. More importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error. Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job. You can supply multiple file_name specifications as a comma-delimited list or in separate DUMPFILE parameter specifications. If no extension is given for the filename, then Export uses the default file extension of .dmp. The filenames can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99. If a file specification contains two substitution variables, both are incremented at the same time. For example, exp%Uaa%U.dmp would resolve to exp01aa01.dmp, exp02aa02.dmp, and so forth. iv 停止JOB Export> stop_job v 启动JOB Export> start_job vi 杀掉JOB Export> kill_job vii 退出交互模式 Export> exit_client viii 指定文件大小 Export> filesize=1G ix 帮助 Export> Help JOB_NAME 指定要导出作用的名称,默认为SYS_XXX JOB_NAME=jobname_string SELECT * FROM DBA_DATAPUMP_JOBS;--查看存在的job NETWORK_LINK导出文件到本地 远程： IP:192.168.10.55,OS:RHEL4.8 64bit,Oracle:10.2.0.5 64bit 被导出用户:monitor 本地： ip: 172.*，WINDOWS XP,oracle 10.2.0.4。 本地tns :cheniwo 本地配置的联系到远程的tns:lobom55 1)确定本地可访问远程 F:\oracle\product\10.2.0\db_2\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP (HOST = 192.168.10.55)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = lobomb))) OK (40 毫秒) 2）在本地建立dblink到远程 F:\Documents and Settings\Administrator>sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 9月 6 14:25:44 2012 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create database link dl_monitor connect to monitor identified by monitor using 'lobom55'; 数据库链接已创建。 SQL> select sysdate from dual@dl_monitor; SYSDATE -------------- 06-9月 -12 3）数据泵导出 F:\Documents and Settings\Administrator>expdp network_link=dl_monitor dumpfile=n etwork_monitor.dmp /*-------------------------------------------------- 说明：network_link是你建立的到远程的database link 下边输入的用户名是你本地数据库的认证 dumpfile 将放在data_pump_dir目录定义的本地目录下 -------------------------------*/ Export: Release 10.2.0.4.0 - Production on 星期四, 06 9月, 2012 14:40:17 Copyright (c) 2003, 2007, Oracle. All rights reserved. 用户名: / as sysdba 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 启动 "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA network_link=dl_monitor dumpfile=network_monitor.dmp 正在使用 BLOCKS 方法进行估计... 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA 使用 BLOCKS 方法的总估计: 81.68 MB 处理对象类型 SCHEMA_EXPORT/USER .............................省略若干行............................. 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_ST ATISTICS 已成功加载/卸载了主表 "SYS"."SYS_EXPORT_SCHEMA_01" ****************************************************************************** SYS.SYS_EXPORT_SCHEMA_01 的转储文件集为: F:\ORACLE\PRODUCT\10.2.0\ADMIN\CHENIWO\DPDUMP\NETWORK_MONITOR.DMP 作业 "SYS"."SYS_EXPORT_SCHEMA_01" 已于 14:51:41 成功完成 4）检查结果： SQL> set head off SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR'; SYS DATA_PUMP_DIR F:\oracle\product\10.2.0\admin\cheniwo\dpdump\ F:\Documents and Settings\Administrator>cd F:\oracle\product\10.2.0\admin\cheniwo\dpdump F:\oracle\product\10.2.0\admin\cheniwo\dpdump>dir /w 驱动器 F 中的卷是 新加卷 卷的序列号是 88F0-A421 F:\oracle\product\10.2.0\admin\cheniwo\dpdump 的目录 [.] [..] cheniwo.sql CHENIWO_DP.DMPdp.log EXPDAT.DMP export.log FULL_CHENIWO.DMP import.log NETWORK_MONITOR.DMP TABLES_CHENIWO.DMP TABLES_CHENIWO2.DMP TS_CHENIWO.DMPUSER_CHENIWO.DMP 12 个文件 128,977,860 字节 2 个目录 155,032,698,880 可用字节 参考至:http://loveboth./blog/1745801 http://bbs./thread-3643706-1-1.html http://www.cnblogs.com/iImax/p/oracle-11g-expdp-interactive.html http://blog.csdn.net/liqfyiyi/article/details/7248911 1 oracle expdp/impdp 用法详解
Data Pump 反映了整个导出/导入过程的完全革新。不使用常见的 SQL 命令，而是应用专用 API（direct path api etc) 来以更快得多的速度加载和卸载数据。 1.Data Pump 导出 expdp 例子： sql>create directory dpdata1 as '/u02/dpdata1'; sql>grant read, write on directory dpdata1 to ananda; $expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES.dmp job_name=CASES_EXPORT $expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export include/exclude 例子： include=table:"in('DB','TS')" 或者include=table:"like '%E%'" 或者include=function,package,procedure,table:"='EMP'" 或者exclude=SEQUENCE,TABLE:"IN ('EMP','DEPT')" 2.Data Pump 导入 expdp 1)从expdp中获取数据源 exp.dmp 2)复制某个数据库中的一个schema到另一个数据库中。 3) 在同一个数据库中把一个schema中所有的对象复制到另一个schema中。 例子： 1)impdp 的数据源是expdp 导出来的DMP文件 impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import 2)复制某个数据库中的一个schema到另一个数据库中。 --1.newwork_link为目标数据库建立的database_link， (用户test 需要grant exp_full_database to TEST; ) create public database link TOLINK connect to TEST identified by oracle using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.20.199)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )'; --2.impdp在目标数据库服务器上执行 只能低版本向高版本imp impdp network_link=TOLINK schemas=test remap_schema=test:link2 3) 在同一个数据库中把一个schema中所有的对象复制到另一个schema中。 --1.创建连接自己的database link: create public database link system_self connect to system identified by "system" using 'orcl'; 数据库链接已创建。 --2.复制hr schema到test schema:
impdp system/system network_link=system_self schemas=hr remap_schema=hr:test ---------------- 一、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。 create directory dpdata1 as 'd:\test\dump'; 二、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错） select * from dba_directories; 三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。 grant read,write on directory dpdata1 to scott; 四、导出数据 1)按用户导 expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1; 2)并行进程parallel expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3 3)按表名导 expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1; 4)按查询条件导 expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'; 5)按表空间导 expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example; 6)导整个数据库 expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y; 五、还原数据 1)导到指定用户下 impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott; 2)改变表的owner impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system; 3)导入表空间 impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example; 4)导入数据库 impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y; 5)追加数据 impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append； ---------------------- Expdp/Impdp的相关参数 EXPDP命令行选项 1. ATTACH 该选项用于在客户会话与已存在导出作用之间建立关联.语法如下 ATTACH=[schema_name.]job_name Schema_name用于指定方案名,job_name用于指定导出作业名.注意,如果使用ATTACH选项,在命令行除了连接字符串和ATTACH选项外,不能指定任何其他选项,示例如下: Expdp scott/tiger ATTACH=scott.export_job 2. CONTENT 该选项用于指定要导出的内容.默认值为ALL CONTENT={ALL | DATA_ONLY | METADATA_ONLY} 当设置CONTENT为ALL 时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义 Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY 3. DIRECTORY 指定转储文件和日志文件所在的目录 DIRECTORY=directory_object Directory_object用于指定目录对象名称.需要注意,目录对象是使用CREATE DIRECTORY语句建立的对象,而不是OS 目录 Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump 建立目录: CREATE DIRECTORY dump as ‘d:dump’; 查询创建了那些子目录: SELECT * FROM dba_directories; 4. DUMPFILE 用于指定转储文件的名称,默认名称为expdat.dmp DUMPFILE=[directory_object:]file_name [,….] Directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指定directory_object,导出工具会自动使用DIRECTORY选项指定的目录对象 Expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp 5. ESTIMATE 指定估算被导出表所占用磁盘空间分方法.默认值是BLOCKS EXTIMATE={BLOCKS | STATISTICS} 设置为BLOCKS时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间,设置为STATISTICS时,根据最近统计值估算对象占用空间 Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump DUMPFILE=a.dump 6. EXTIMATE_ONLY 指定是否只估算导出作业所占用的磁盘空间,默认值为N EXTIMATE_ONLY={Y | N} 设置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作. Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y 7. EXCLUDE 该选项用于指定执行操作时释放要排除对象类型或相关对象 EXCLUDE=object_type[:name_clause] [,….] Object_type用于指定要排除的对象类型,name_clause用于指定要排除的具体对象.EXCLUDE和INCLUDE不能同时使用 Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW 8. FILESIZE 指定导出文件的最大尺寸,默认为0,(表示文件尺寸没有限制) 9. FLASHBACK_SCN 指定导出特定SCN时刻的表数据 FLASHBACK_SCN=scn_value Scn_value用于标识SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同时使用 Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_SCN=358523 10. FLASHBACK_TIME 指定导出特定时间点的表数据 FLASHBACK_TIME=”TO_TIMESTAMP(time_value)” Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME= “TO_TIMESTAMP(’25-08-2004 14:35:00’,’DD-MM-YYYY HH24:MI:SS’)” 11. FULL 指定数据库模式导出,默认为N FULL={Y | N} 为Y时,标识执行数据库导出. 12. HELP 指定是否显示EXPDP命令行选项的帮助信息,默认为N 当设置为Y时,会显示导出选项的帮助信息. Expdp help=y 13. INCLUDE 指定导出时要包含的对象类型及相关对象 INCLUDE = object_type[:name_clause] [,… ] 14. JOB_NAME 指定要导出作用的名称,默认为SYS_XXX JOB_NAME=jobname_string 15. LOGFILE 指定导出日志文件文件的名称,默认名称为export.log LOGFILE=[directory_object:]file_name Directory_object用于指定目录对象名称,file_name用于指定导出日志文件名.如果不指定directory_object.导出作用会自动使用DIRECTORY的相应选项值. Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log 16. NETWORK_LINK 指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项. 17. NOLOGFILE 该选项用于指定禁止生成导出日志文件,默认值为N. 18. PARALLEL 指定执行导出操作的并行进程个数,默认值为1 19. PARFILE 指定导出参数文件的名称 PARFILE=[directory_path] file_name 20. QUERY 用于指定过滤导出数据的where条件 QUERY=[schema.] [table_name:] query_clause Schema 用于指定方案名,table_name用于指定表名,query_clause用于指定条件限制子句.QUERY选项不能与 CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES等选项同时使用. Expdp scott/tiger directory=dump dumpfiel=a.dmp Tables=emp query=’WHERE deptno=20’ 21. SCHEMAS 该方案用于指定执行方案模式导出,默认为当前用户方案. 22. STATUS 指定显示导出作用进程的详细状态,默认值为0 23. TABLES 指定表模式导出 TABLES=[schema_name.]table_name[:partition_name][,…] Schema_name用于指定方案名,table_name用于指定导出的表名,partition_name用于指定要导出的分区名. 24. TABLESPACES 指定要导出表空间列表 25. TRANSPORT_FULL_CHECK 该选项用于指定被搬移表空间和未搬移表空间关联关系的检查方式,默认为N. 当设置为Y时,导出作用会检查表空间直接的完整关联关系,如果表空间所在表空间或其索引所在的表空间只有一个表空间被搬移,将显示错误信息.当设置为N时, 导出作用只检查单端依赖,如果搬移索引所在表空间,但未搬移表所在表空间,将显示出错信息,如果搬移表所在表空间,未搬移索引所在表空间,则不会显示错误信息. 26. TRANSPORT_TABLESPACES 指定执行表空间模式导出 27. VERSION 指定被导出对象的数据库版本,默认值为COMPATIBLE. VERSION={COMPATIBLE | LATEST | version_string} 为COMPATIBLE时,会根据初始化参数COMPATIBLE生成对象元数据;为LATEST时,会根据数据库的实际版本生成对象元数据.version_string用于指定数据库版本字符串.调用EXPDP 使用EXPDP工具时,其转储文件只能被存放在DIRECTORY对象对应的OS目录中,而不能直接指定转储文件所在的OS目录.因此, 使用EXPDP工具时,必须首先建立DIRECTORY对象.并且需要为数据库用户授予使用DIRECTORY对象权限. |
|
|
