用SQL管理数据库--数据泵 Data Pump
时间：2010-12-20 来源：x-i-x
1. 作用
高速数据通道，数据库-数据库 or 数据库-系统文件，schema 之间，table之间，tablespace之间
2. 先决条件
只运行于服务端，创建数据目录，日志目录
3. 用法
expdp, impdp, dbms_datapump pl/sql package
help=y 可查询所有可用参数
EXP
Database
Ø Role
EXP_FULL_DATABASE
Ø Directory
chap7a,chap7b eg.
Ø Cmd
expdp system/secret full=Y dumpfile=chap7a:fulla%U.dmp, chap7b:fullb%U.dmp filesize 2G parallel=2 logfile=chap7:full.log
Ø 参数
full=y 数据库模式
%U 文件按序号排列 00-99 ，每个文件2G
Schema
Ø Role
EXP_FULL_DATABASE 非本用户schema需要
Ø Cmd
expdp hr/hr dumpfile=chap7:hr.dmp logfile=chap7:hr.out
Ø 参数
full=y 不需要
schemas=schema_list 多个用逗号分隔，如果导出本用户的schema不需要该参数
Table
Ø Cmd
expdp hr/hr dumpfile=chap7:job_tabs.dmp nologfile=y content=metadata_only tables=jobs,job_history
Ø 参数
nologfile=y 不写日志
content=metadata_only 只导出元数据（表结构等）
Tablespace
Ø Role
EXP_FULL_DATABASE
Ø Cmd
expdp system/password dumpfile=chap7:users_ts.dmp logfile=chap7:users_ts.out tablespaces=users
Ø 参数
tablespaces= 多个表空间用逗号分隔
Ø 其它参数（可选）：
network_link=db_link 数据源为远程数据库
content= DATA_ONLY 或者 METADATA_ONLY ,
parallel=degree 导入时候可以设置最大进程/线程数
parfile= dir:file 导入时候的参数文件 dir =database directory object ，file=file name
job_name= The name of the job. The name is limited to 30 characters; it will be truncated if more than 30 characters are used. It may consist of printable characters and spaces. It is implicitly qualified by the schema of the user executing the OPEN procedure and must be unique to that schema (that is, there cannot be other Data Pump jobs using the same name).
The name is used to identify the job both within the API and with other database components such as identifying the job in the DBA_RESUMABLE view if the job becomes suspended through lack of resources. If no name is supplied, a system generated name will be provided for the job in the following format: "SYS_<OPERATION>_<MODE>_%N".
The default job name is formed where %N expands to a two-digit incrementing integer starting at '01' (for example, "SYS_IMPORT_FULL_03"). The name supplied for the job will also be used to name the master table and other resources associated with the job.
EM Control
Ø 步骤
点击Export to files >> 选择export类型 >> 提交，等待作业队列输出
IMP
先决条件
Ø IMP_FULL_DATABASE 从其它活动库导入需要，或者导出会话需要EXP_FULL_DATABASE
参数
Ø reuse_datafiles=[Y/N] ，Create TABLESPACE 时是否重写overwrite数据文件datafile
Ø remap_ ：导入对象重命名
Ø include=object_list; 导入的对象
Ø exclude=object_list; 导入排除的对象
Cmd
Ø impdp system/password full=y dumpfile=chap7:FULL.DMP nologfile=y sqlfile=chap7:FULL.SQL
参数：读取dump file FULL.DMP 中所有的DDL语句，存放到文件FULL.SQL中，不需要写日志
Ø impdp ystem/password network_link=prod schemas="HR" remap_schema="HR:HR_TEST" content=metadata_only logfile=chap7:HR_TEST.imp
参数：network_link=prod 通过数据库链接prod，
schemas="HR" 导入表空间HR
remap_schema="HR:HR_TEST" 导入到本地表空间HR_TEST
content=metadata_only 只导入结构
logfile=chap7:HR_TEST.imp 导入日志 HR_TEST.imp
Ø impdp system/password full=y dumpfile=chap7:HR.DMP nologfile=y sqlfile=chap7:HR_proc_give.SQL include=PROCEDURE:"LIKE 'GIVE%'"
参数：include=PROCEDURE:"LIKE 'GIVE%'" 导入名字包含GIVE的存储过程
Ø impdp system/password network_link=prod schemas="HR" remap_schema="HR:HR_TEST" content=data_only include=TABLE:"= 'DEPARTMENTS'" logfile=chap7:HR_TEST.imp
参数： include=TABLE:"= 'DEPARTMENTS'" 只导入表HR.DEPARTMENTS
EM Control
Ø 步骤
Control main menu >> Maintenance >> Import From Files/ Database (live) >> 输入文件路径/数据库链接 >> 输入 remapping 名称
[ >> 高级选项 >> 输入数据过滤条件 >> 设定job名称和执行时间 ]
监控任务
Ø Monitor Process of a Data Pump Job
数据字典
Ø DBA_DATAPUMP_JOBS
相关阅读 更多 +
