2.2.1.数据泵 EXPDP 导出工具的使用
转载自:https://blog.csdn.net/leshami/article/details/5925592
对于Oracle 数据库之间的导入导出,可以使用Oracle提供的导入导出工具EXP/IMP来实现。EXP/IMP是Oracle早期提供的数据导入导出工具。在Oracle 10g 中,提供了高速导入导出数据泵IMPDP,EXPDP,本文主要讲述EXPDP的用法。
一、数据泵的体系结构
数据泵是一个基于服务器端的高速导入导出工具,通过dbms_datapump包来调用 提供expdp,impdp,以及基于Web页面来实现导入导出 提供两种数据数据方式方式:直接路径、外部表 可以定制数据泵作业,以及从作业中分离和重新附加到作业 服务器端的数据泵是直接访问数据文件与SGA,不必通过会话进行访问 数据泵进程 对Unix系统而言,数据泵进程为expdp,impdp 对Windows系统而言,数据泵进程为expdp.exe,impdp.exe 启动一个DataPump作业,至少会启动下列两个进程,一个Data Pump Master(DMnn),一个或多个工作进程(DWnn),主进程控制工作进程 如果多个DataPump作业同时运行,那么每个作业都具有自己的DMnn进程以及自己的DWnn进程 如果设置了并行技术,则每个DWnn进程可以使用两个或多个并行执行服务器(名称为Pnnn) DataPump生成下列三种形式的文件 SQL文件:描述指定作业所包含对象的若干DDL语句 转储文件:即包含数据和元数据的文件 日志文件:用于记录导出时的相关信息 目录 用于设置导入导出文件所在或存放的位置 create directory dump_scott as /home/oracle/dump/scott'; 可以通过dba_directories来查看系统中已创建的目录 select * from dba_directories; 对于创建的目录必须授予用户读写目录的权限 grant read,write on directory dump_scott to scott;
二、数据泵的优点
在Oracel 10g 中提供的数据泵,较之i时代的导入导出工具(imp,exp),除了能实现imp/exp的功能之外,提供了更好的性能, 下面是数据泵的优点 为数据及数据对象提供更细微级别的选择性(使用exclude,include,content参数) 可以设定数据库版本号(主要是用于兼容老版本的数据库系统) 并行执行 预估导出作业所需要的磁盘空间(使用estimate_only参数) 支持分布式环境中通过数据库链接实现导入导出 支持导入时重新映射功能(即将对象导入到新的目标数据文件,架构,表空间等) 支持元数据压缩及数据采样
三、数据泵程序接口及模式
数据泵导入导出接口如下 命令行接口 参数文件 交互式命令行接口 数据库控制台 数据泵导入导出模式 整个数据库 架构 表 表空间 传输表空间
四、导出工具expdp
--1. 它是操作系统下一个可执行的文件存放目录/ORACLE_HOME/bin [oracle@oradb bin]$ ls -lh expdp -rwxr-x--x 1 oracle oinstall 174K Sep 13 20:01 expdp expdp导出工具将数据库中数据备份压缩成一个二进制系统文件.可以在不同OS间迁移 expdb支持三种模式: a. 表模式: 导出用户所有表或者指定的表 b. 用户模式:导出用户所有对象以及对象中的数据 c. 导出表空间:导出数据库中特定的表空间 d. 整个数据库: 导出数据库中所有对象 使用expdp-? 可以查看expdp命令的用法并启动交互进程,也可使用expdp -help来查看更详细的帮助信息 [oracle@oradb bin]$ expdp -? abort_step Undocumented feature access_method Data Access Method - default is Automatic attach Attach to existing job - no default)'' compression Content to export: default is METADATA_ONLY content Content to export: default is ALL directory Default directory specification dumpfile dumpfile names: format is (file1,...) default is expdat.dmp encryption_password Encryption key to be used estimate Calculate size estimate: default is BLOCKS estimate_only Only estimate the length of the job: default is N exclude Export exclude option: no default filesize file size: the size of export dump files flashback_time database time to be used for flashback export: no default flashback_scn system change number to be used for flashback export: no default full indicates a full mode export include export include option: no default ip_address IP Address for PLSQL debugger help help: display description on export parameters, default is N job_name Job Name: no default)'' keep_master keep_master: Retain job table upon completion log_entry logentry logfile log export messages to specified file metrics Enable/disable object metrics reporting mp_enable Enable/disable multi-processing for current session network_link Network mode export nologfile No export log file created package_load Specify how to load PL/SQL objects parallel Degree of Parallelism: default is 1 parallel_threshold Degree of DML Parallelism parfile parameter file: name of file that contains parameter specifications query query used to select a subset of rows for a table sample Specify percentage of data to be sampled schemas schemas to export: format is '(schema1, .., schemaN)' silent silent: display information, default is NONE status Interval between status updates tables Tables to export: format is '(table1, table2, ..., tableN)' tablespaces tablespaces to transport/recover: format is '(ts1,..., tsN)' trace Trace option: enable sql_trace and timed_stat, default is 0 transport_full_check TTS perform test for objects in recovery set: default is N transport_tablespaces Transportable tablespace option: default is N tts_closure_check Enable/disable transportable containment check: def is Y userid user/password to connect to oracle: no default version Job version: Compatible is the default Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 14:22:56 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: --2. 导出工具expdp非交互式命令行方式的例子 --a.基于表模式的导出 SQL> create directory dump_scott as '/home/oracle/dump/scott'; Directory created. SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------- SYS DUMP_SCOTT /home/oracle/dump/scott SQL> grant read,write on directory dump_scott to scott; Grant succeeded. SQL> ! [oracle@oradb /]$ mkdir /home/oracle/dump [oracle@oradb /]$ mkdir /home/oracle/dump/scott [oracle@oradb ~]$ expdp scott/tiger directory=dump_scott dumpfile=dumptab.dmp / > logfile=scott.log tables=dept,emp Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 14:55:23 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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/********/ directory=dump_scott dumpfile=dumptab.dmp logfile=scott.log tables=dept,emp Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.656 KB 4 rows . . exported "SCOTT"."EMP" 7.820 KB 14 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /home/oracle/dump/scott/dumptab.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:55:56 --后台中DMnn,DWnn进程为启动DataPump是产生的进程 [oracle@oradb /]$ ps -ef | grep ora_d oracle 3445 1 0 14:19 ? 00:00:00 ora_dbw0_orcl oracle 3461 1 0 14:19 ? 00:00:00 ora_d000_orcl [oracle@oradb ~]$ ls -lh /home/oracle/dump/scott total 132K -rw-r----- 1 oracle oinstall 124K Sep 20 14:55 dumptab.dmp -rw-r--r-- 1 oracle oinstall 1.4K Sep 20 14:55 scott.log --b. 基于用户模式导出 [oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=dumpscott.dmp schemas=scott Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 15:08:55 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 "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********/ directory=dump_scott dumpfile=dumpscott.dmp schemas=scott Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.656 KB 4 rows . . exported "SCOTT"."EMP" 7.820 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: /home/oracle/dump/scott/dumpscott.dmp Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:09:23 --c.基于表空间导出 [oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=users1.dmp,user2.dmp / > compression tablespaces=users Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 15:17:35 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 "SCOTT"."SYS_EXPORT_TABLESPACE_01": scott/********/ directory=dump_scott dumpfile=users1.dmp,user2.dmp compression tablespaces=users Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.656 KB 4 rows . . exported "SCOTT"."EMP" 7.820 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLESPACE_01 is: /home/oracle/dump/scott/users1.dmp Job "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully completed at 15:17:51 [oracle@oradb /]$ ls -lh /home/oracle/dump/scott total 524K -rw-r----- 1 oracle oinstall 4.0K Sep 20 15:17 compression.dmp -rw-r----- 1 oracle oinstall 224K Sep 20 15:09 dumpscott.dmp -rw-r----- 1 oracle oinstall 124K Sep 20 14:55 dumptab.dmp -rw-r--r-- 1 oracle oinstall 1.6K Sep 20 15:17 export.log -rw-r--r-- 1 oracle oinstall 1.4K Sep 20 14:55 scott.log -rw-r----- 1 oracle oinstall 4.0K Sep 20 15:17 user2.dmp -rw-r----- 1 oracle oinstall 148K Sep 20 15:17 users1.dmp --d. 导出整个数据库,且使用并行导出方式 [oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=full20_%u.dmp parallel=4 full=y Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 15:24:02 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 ORA-31631: privileges are required ORA-39161: Full database jobs require privileges [oracle@oradb /]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 20 15:24:16 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected. Grant succeeded. SQL> ! [oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=full20_%u.dmp parallel=6 full=y --中间过程省略 [oracle@oradb dump]$ ls -lh ./scott/fu* -rw-r----- 1 oracle oinstall 19M Sep 20 15:36 ./scott/full20_01.dmp -rw-r----- 1 oracle oinstall 22M Sep 20 15:34 ./scott/full20_02.dmp -rw-r----- 1 oracle oinstall 18M Sep 20 15:36 ./scott/full20_03.dmp -rw-r----- 1 oracle oinstall 15M Sep 20 15:36 ./scott/full20_04.dmp -rw-r----- 1 oracle oinstall 5.4M Sep 20 15:36 ./scott/full20_05.dmp -rw-r----- 1 oracle oinstall 196K Sep 20 15:33 ./scott/full20_06.dmp [oracle@oradb dump]$ ps -ef | grep ora_d oracle 3445 1 0 14:19 ? 00:00:01 ora_dbw0_orcl oracle 3461 1 0 14:19 ? 00:00:00 ora_d000_orcl oracle 23443 1 4 15:32 ? 00:00:01 ora_dm00_orcl oracle 23494 1 23 15:32 ? 00:00:08 ora_dw01_orcl oracle 23673 1 11 15:33 ? 00:00:02 ora_dw02_orcl oracle 23675 1 16 15:33 ? 00:00:03 ora_dw03_orcl oracle 23677 1 8 15:33 ? 00:00:01 ora_dw04_orcl oracle 23679 1 5 15:33 ? 00:00:00 ora_dw05_orcl oracle 23681 1 2 15:33 ? 00:00:00 ora_dw06_orcl oracle 23696 2416 0 15:33 pts/1 00:00:00 grep ora_d
五、数据泵的监控
--1.查询dba_directories获得所创建的目录 --2.可以查询dba_datapump_jobs来查看数据泵作业的运行情况,也可以利用ATTACH重新连接上还在进行的JOB 每个datapump可以通过job_name 参数来指定作业名称,如未指定,则系统使用默认的作业名称,如下面的视图中为SYS_EXPORT_FULL_01 通过v$session_longops也可以查看长时间运行的datapump job的具体内容 SQL> select owner_name owr,job_name jbn,operation ope,job_mode jbm,state,degree, 2 attached_sessions atts,datapump_sessions dats 3 from dba_datapump_jobs; OWR JBN OPE JBM STATE DEGREE ATTS DATS ---------- -------------------- -------------------- --------------- ---------- ---------- ---------- ---------- SCOTT SYS_EXPORT_FULL_01 EXPORT FULL COMPLETING 2 1 2 SQL> select sid, serial#,session_type 2 from v$session s, dba_datapump_sessions d 3 where s.saddr = d.saddr; SID SERIAL# SESSION_TYPE ---------- ---------- -------------- 143 10 DBMS_DATAPUMP 149 37 MASTER 132 3 WORKER 136 3 WORKER 135 4 WORKER 141 5 WORKER 128 2 WORKER 142 4 WORKER --3.监控数据泵的逻辑备份程度 SELECT sid, serial#, context, sofar, totalwork, ROUND(sofar/totalwork*100,2) "%_COMPLETE" FROM v$session_longops WHERE opname LIKE '%EXP%' AND totalwork != 0 AND sofar <> totalwork; SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE 130 33 0 54 70 77.14
六、expdp的常用参数
1.content: 该选项用于指定要导出的内容.默认值为ALL CONTENT={ALL | DATA_ONLY | METADATA_ONLY} expdp scott/tiger schemas=scott content=all expdp scott/tiger tables=emp content=data_only directory=dump_scott dumpfile=empdata.dmp(只导出对象数据) expdp scott/tiger tables=emp content=metadata_only directory=dump_scott dumpfile=empmd.dmp(只有定义信息) 2.estimate: 指定估算被导出表所占用磁盘空间分方法.默认值是blocks expdp scott/tiger directory=dump_scott dumpfile=dba_object.dmp tablespaces=users estimate=statistics expdp scott/tiger directory=dump_scott dumpfile=dba_object.dmp tablespaces=users estimate=blocks 3.extimate_only:指定是否只估算导出作业所占用的磁盘空间,默认值为N expdp scott/tiger schemas=scott estimate_only=y 设置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业, 为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作. 4.exclude:该选项用于指定执行操作时释放要排除对象类型或相关对象 exclude=view exclude=package exclude=index:"like 'EMP%' object_type用于指定要排除的对象类型,name_expr用于指定要排除的具体对象.exclude和include不能同时使用 expdp scott/tiger schemas=scott exclude=view dumpfile=a9.dmp include = object_type[:"name_expr"] 5.filesize:指定导出文件的最大尺寸,默认为,(表示文件尺寸没有限制) 6.flashback_scn: 前提闪回功能开启 expdp scott/tiger tables=emp dumpfile=e2.dmp flashback_scn=4284715 如果闪回的时间点该对象结构发生变化,将报错(比如该对象没有创建或者ddl操作) 7.flashback_time:指定导出特定时间点的表数据 expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME="TO_TIMESTAMP(’-08-2004 14:35:00’,’DD-MM-YYYY HH24:MI:SS’)" windows下: C:/>expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=a.dmp flashback_time=/"TO_TIMESTAMP('06-04-2010 11:24:26', 'DD-MM-YYYY HH24:MI:SS')/" 8.query导出查询得到的结果集 query=scott.emp :"where deptno = 30 and sal > 3500" 9.sample 使用该参数进行对导出的数据进行采样 sample="scott"."emp":20 expdp scott/tiger directory=dump_scott dumpfile=sam.dmp sample=30 10.dumpfile 指定导出时的文件名 dumpfile=scott_tb.dmp dumpfile=scott_tb_%u.dmp %u 用于扩展导出的文件名,固定长度为个字符,从开始递增,使用并行导出时建议指定该参数