[toc]
一、 exp参数使用
1. exp参数的查看
切换至 Oracle 用户，进入 Oracle 目录，执行：
exp help=y
查看 exp 相关参数。
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
2. 参数详解
| 关键字 | 说明 | 例子/作用 |
|---|---|---|
| USERID | 指定登陆exp的用户名和密码 | exp userid=sys/sys@orcl tables=(swserp.tbsuser) file=’/tmp/e.dmp’; |
| BUFFER | 数据缓冲区大小 | 所有者用户名列表 数据量大的时候，设置buffer调大可以提高速度 |
| FILE | 输出文件 (EXPDAT.DMP) | 表名列表 |
| COMPRESS | 导入到一个区 (Y) | IO 记录的长度 |
| GRANTS | 导出权限 (Y) | 增量导出类型 |
| INDEXES | 导出索引 (Y) | 跟踪增量导出 (Y) |
| DIRECT | 直接路径 (N) | 导出触发器 (Y) |
| LOG | 屏幕输出的日志文件 | 分析对象 (ESTIMATE) |
| ROWS | 导出数据行 (Y) | 参数文件名 |
| CONSISTENT | 交叉表的一致性 (N) | 导出的约束条件 (Y) |
| OBJECT_CONSISTENT | 只在对象导出期间设置为只读的事务处理 (N) | |
| FEEDBACK | 每 x 行显示进度 (0) | |
| FILESIZE | 每个转储文件的最大大小 | |
| FLASHBACK_SCN | 用于将会话快照设置回以前状态的 SCN | |
| FLASHBACK_TIME | 用于获取最接近指定时间的 SCN 的时间 | |
| QUERY | 用于导出表的子集的 select 子句 导出指定条件的数据,该参数需要跟tables一起使用 | exp userid=hxl/hxl@orcl file=c:\query.dmp,tables=(tb_exp_test),query=(‘Where owner = ‘‘HXL’’ And Object_name=’‘ERROR_LOG’’ And last_ddl_time = to_date(’‘2010-9-13 16:43:33’’,’‘YYYY-MM-DD HH24:MI:SS’’)’), log=c:\explog.txt |
| RESUMABLE | 遇到与空格相关的错误时挂起 (N) | |
| RESUMABLE_NAME | 用于标识可恢复语句的文本字符串 | |
| RESUMABLE_TIMEOUT | RESUMABLE 的等待时间 | |
| TTS_FULL_CHECK | 对 TTS 执行完整或部分相关性检查 | |
| TABLESPACES | 要导出的表空间列表 导出指定表空间下所有的对象 | exp ‘sys/sys as sysdba’ file=c:\tsp_test.dmp transport_tablespace=Y tablespaces=test |
| TRANSPORT_TABLESPACE | 导出可传输的表空间元数据 (N) 导出的时候需要将准备导出的表空间设置为只读 | |
| TEMPLATE | 调用 iAS 模式导出的模板名 |
2. EXP导出与字符集
导出客户端的字符集的设置会影响导出数据，所以应该设置导出客户端字符集和数据库相一致。
在Linux/UNIX上Bash下通常可以如下设置：
[oracle@localhost bin]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
数据库端使用的字符集可以通过如下查询获得：
SQL> select * from NLS_DATABASE_PARAMETERS where parameter='LS_CHARACTERSET';
PARAMETER VALUE
NLS_CHARACTERSET ZHS16GBK
3. 带查询子句的部分导出
从Oracle 8i开始，EXP工具支持使用查询子句对特定表的部分数据执行导出，这个功能是通过EXP的query参数来实现的，在使用过程中可能最常见的错误是：
LRM-00112: multiple values not allowed for parameter ‘query’
EXP-00019: failed to process parameters, type ‘EXP HELP=Y’ for help
EXP-00000: Export terminated unsuccessfully
这通常是因为Query子句包含特殊字符导致的，由于特殊字符在命令行通常需要转义，所以导出的语法结构通常与众不同。
在Linux/UNIX的Bash下，通常可以如下指定query参数：
exp … query=“where col < 1000”
exp … query=“where col < ‘1000’”
以下是一个简单的举例：
[oracle@danaly ~]$ exp eygle/eygle file=test.dmp tables=tquery query=“where object_id < 1000”
Export: Release 10.2.0.1.0 - Production on Thu Feb 1 11:47:54 2007
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
. . exporting table TQUERY 949 rows exported
Export terminated successfully without warnings.
3. 参数文件的使用
对于类似以上的复杂处理，可以通过参数PARFILE来指定一个参数文件，具体的参数写在参数文件中，这样就可以避免复杂的转义等操作：
[oracle@danaly ~]$ cat parfile.lst
userid=eygle/eygle
file=test.dmp
tables=tquery
query=“where object_id < 1000”
[oracle@danaly ~]$ exp parfile=parfile.lst
Export: Release 10.2.0.1.0 - Production on Thu Feb 1 11:52:30 2007
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
. . exporting table TQUERY 949 rows exported
Export terminated successfully without warnings.
4. 使用通配符进行模糊导出
有时候，我们希望按条件导出部分表，EXP工具支持模糊查找，通配符可以在tables参数中使用，例如：
[oracle@jumper oracle]$ exp eygle/eygle tables=e% file=a.dmp
Export: Release 9.2.0.4.0 - Production on Mon Mar 26 14:59:14 2007
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
. . exporting table EYGLE 9 rows exported
. . exporting table EYGLE_ACCESS_LOG_20061016
Export terminated successfully without warnings.
这样，以字母E开头的表都被导出了。
5. 按日期区分文件名称的导出
很多时候，在导出备份时，我们希望在文件名中加入日期变量，以区分不同日期的导出文件，也可以防止覆盖。
在导出文件名中引入时间有很多不同的做法，本文主要介绍以下两种方法：
（1）通过数据库查询获取日期。
通过数据库查询可以获得需要的日期格式，在Windows上可以编写两个文件用于完成备份，一个是可执行的批处理文件，名称为startbak.bat，可以包含如下内容：
echo off
set oracle_sid=eygle
sqlplus eygle/eygle @expbydt.sql
第二个文件是expbydt.sql脚本：
column today new_val dt
select to_char( sysdate, ‘ddmmyyyy’ ) today from dual;
host exp userid=eygle/eygle file=exp_eygle_&dt…dmp log=exp_eygle_&dt…log
exit
准备好了这两个文件之后，就可以通过命令行运行startbak.bat来执行逻辑备份了：
D:\backup>startbak.bat
D:\backup>echo off
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
TODAY
08022007
Export: Release 10.2.0.1.0 - Production on 星期四 2月 8 15:55:07 2007
Copyright © 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的用户…
. 正在导出 pre-schema 过程对象和操作
。。。。
即将导出 EYGLE 的对象…
. 即将导出 EYGLE 的表通过常规路径…
. . 正在导出表 EYGLE导出了 1 行
. . 正在导出表 EYGLE_BLOB导出了 8 行
. . 正在导出表 TEST导出了 15 行
. 正在导出同义词
……
. 正在导出统计信息
成功终止导出, 没有出现警告。
检查一下导出后的文件名称，如果备份计划无误，现在就可以通过Windows上的定时任务来定时执行这个任务了。
D:\backup>dir
2007-02-08 15:54 163 expbydt.sql
2007-02-08 15:55 1,699,840 exp_eygle_08022007.dmp
2007-02-08 15:55 1,091 exp_eygle_08022007.log
2007-02-08 15:51 64 startbak.bat
（2）通过操作系统获取日期。
时间变量也可以直接通过操作系统获得，在Windows上，我们可以通过如下命令获得：
D:\backup>echo %date:~0,10%
2007-02-08
当然，在Windows上的时间格式和区域设置有关，如果以上命令输出的日期格式存在问题，那么可以调整一下区域设置的日期。
可以通过一个名为expbdt.bat的批处理文件来执行导出操作：
echo off
set oracle_sid=eygle
exp userid=eygle/eygle full=y file=d:\backup\exp_eygle_%date:~0,10%.dmp log=d:\backup\exp_eygle_%date:~0,10%.log
执行的结果和上面的方法类似：
D:\backup>expbdt.bat
D:\backup>echo off
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出整个数据库…
. 正在导出表空间定义
…
. 正在导出 pre-schema 过程对象和操作
. 正在导出簇定义
. 即将导出 SYSTEM 的表通过常规路径…
. . 正在导出表 DEF_AQCALL导出了 0 行 . . 正在导出表 DEF_AQERROR导出了 0 行
…
. 正在导出默认值和系统审计选项
. 正在导出统计信息
成功终止导出, 没有出现警告。
导出的输出如下：
D:\backup>dir
2007-02-08 16:46 134 expbdt.bat
2007-02-08 16:47 11,440,128 exp_eygle_2007-02-08.dmp
2007-02-08 16:47 29,646 exp_eygle_2007-02-08.log
在Linux/UNIX上的时间定义就显得更为简单：
exp eygle/eygle file=eygle_date +%Y%M%d
导出文件格式如下：
[oracle@jumper oracle]$ ls -l *.dmp
-rw-r–r-- 1 oracle dba 32768 Feb 8 16:41 eygle_20074008.dmp
6. 应用
EXP常用选项：
- FULL，这个用于导出整个数据库，在ROWS=N一起使用时，可以导出整个数据库的结构。例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y
- OWNER和TABLE，这两个选项用于定义EXP的对象。OWNER定义导出指定用户的对象；TABLE指定EXP的table名称，例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log owner=duanl
exp userid=test/test file=./db_str.dmp log=./db_str.log table=nc_data,fi_arap
- BUFFER和FEEDBACK，在导出比较多的数据时，我会考虑设置这两个参数。例如：
exp userid=test/test file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT
- FILE和LOG，这两个参数分别指定备份的DMP名称和LOG名称，包括文件名和目录，例子见上面。
二、 imp参数使用
1. imp参数的查看
切换至 Oracle 用户，进入 Oracle 目录，执行：
imp help=y
查看 imp 相关参数。
[oracle@localhost bin]$ imp help=y
Import: Release 19.0.0.0.0 - Production on Mon Nov 1 11:28:45 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
You can let Import prompt you for parameters by entering the IMP
command followed by your username/password:
Example: IMP SCOTT/TIGER
Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:
Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE input files (EXPDAT.DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
INDEXES import indexes (Y) COMMIT commit array insert (N)
ROWS import data rows (Y) PARFILE parameter filename
LOG log file of screen output CONSTRAINTS import constraints (Y)
DESTROY overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)
FEEDBACK display progress every x rows(0)
TOID_NOVALIDATE skip validation of specified type ids
FILESIZE maximum size of each dump file
STATISTICS import precomputed statistics (always)
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
COMPILE compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION import streams general metadata (Y)
STREAMS_INSTANTIATION import streams instantiation metadata (N)
DATA_ONLY import only data (N)
VOLSIZE number of bytes in file on each volume of a file on tape
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set
Import terminated successfully without warnings.
[oracle@localhost bin]$
2. 参数详解
| 关键字 | 说明 | 例子/作用 |
|---|---|---|
| USERID | 用户名/口令 | |
| FULL | 导入整个文件 | imp file=c:\exptest.dmp full=Y |
| BUFFER | 数据缓冲区大小 | imp file=c:\exptest.dmp full=Y buffer=2048000 |
| FROMUSER | 所有人用户名列表 具体指定那个用户下的对象需要导入,用户名列表需要放在括号中(fromuser=test这样好像是不行的) | 单独导入test用户的对象 imp file=c:\exptest.dmp fromuser=(test) |
| FILE | 输入文件 | |
| TOUSER | 用户名列表 具体指定导入那个用户,该参数必须跟fromuser一起使用 | 将test用户下的对象导入到hxl用户下imp file=c:\exptest.dmp fromuser=(test) touser=(hxl); |
| SHOW | 只列出文件内容 在屏幕显示文件内容,具体不会做导入操作 | imp file=c:\exptest.dmp full=y show=Y; |
| TABLES | 表名列表 导入表名列表,多个表名,逗号分隔 | imp test file=c:\exptest.dmp tables=(tb_exp_test,tb_exp_test_01) 导入表tb_exp_test和tb_exp_test_01 |
| IGNORE | 忽略创建错误 忽略导入过程中创建对象的错误(确保数据能够导入),但不会忽略写入数据的错误(如导入的数据跟当前表里的数据有违反唯一性约束的错误) | imp file=c:\exptest.dmp full=y ignore=Y; |
| RECORDLENGTH | IO记录的长度，最大值为65535 | imp file=c:\exptest.dmp full=Y recordlength=65535 |
| GRANTS | 导入权限 | imp file=c:\exptest.dmp full=y grants=Y; |
| INCTYPE | 增量导入类型 | |
| INDEXES | 导入索引 indexes为Y的时候，将导入索引,否则不导入 | imp test file=c:\exptest.dmp tables=(tb_exp_test) indexes=Y;数据加载到表tb_exp_test，并创建索引 |
| COMMIT | 提交数组插入，该参数为Y时，导入的过程会提交数据行,若buffer设置得太小,则会过去频繁的提交数据,对性能有一定的影响 | |
| ROWS | 导入数据行 该参数为Y的时候将导入数据,默认是导入数据.但出于某种原因可以将该参数设置成N即不导入数据行 | imp test file=c:\exptest.dmp tables=(tb_exp_test) rows=Y constraints=N indexes=N;先加载数据到表tb_exp_test，但不创建约束和索引 |
| PARFILE | 参数文件名 将个参数写入parfile指定的文件中 | imp parfile=c:\pfile.txt pfile.txt的内容如下: file=c:\exptest.dmp full=y commit=N; |
| LOG | 屏幕输出的日志文件 | |
| CONSTRAINTS | 导入限制 constraints为Y的时候，将导入约束,如主键,外键,check,否则不导入 | imp test file=c:\exptest.dmp tables=(tb_exp_test) constraints=Y;数据加载到表tb_exp_test，并创建各种约束 |
| DESTROY | 覆盖表空间数据文件 | |
| INDEXFILE | 将表/索引信息写入指定的文件 该参数该创建索引的DDL导出到指定文件中,只用该参数不会导入数据 | imp test file=c:\exptest.dmp tables=(tb_exp_test) constraints=Y indexes=Y indexfile=C:\index.txt 将该表创建索引的DDL和创建各种constraints以及创建表的的DDL导入到C盘的文件index.txt中， 但数据未加载到tb_exp_test中.(文件中创建表和约束的DDL已经加入注释).在命令行模式中可以直接运行(@index.txt)创建索引 |
| SKIP_UNUSABLE_INDEXES | 跳过不可用索引的维护 | |
| FEEDBACK | 每 x 行显示进度 | |
| TOID_NOVALIDATE | 跳过指定类型 ID 的验证 | |
| FILESIZE | 每个转储文件的最大大小 | |
| STATISTICS | 始终导入预计算的统计信息 | |
| RESUMABLE | 在遇到有关空间的错误时挂起 | |
| RESUMABLE_NAME | 用来标识可恢复语句的文本字符串 | |
| RESUMABLE_TIMEOUT | RESUMABLE 的等待时间 | |
| COMPILE | 编译过程, 程序包和函数 | |
| STREAMS_CONFIGURATION | 导入 Streams 的一般元数据 | |
| STREAMS_INSTANITATION | 导入 Streams 的实例化元数据 | |
| TRANSPORT_TABLESPACE | 导入可传输的表空间元数据 | |
| TABLESPACES | 将要传输到数据库的表空间 | |
| DATAFILES | 将要传输到数据库的数据文件 | |
| TTS_OWNERS | 拥有可传输表空间集中数据的用户 |
在Oracle中，imp工具的ignore参数有什么作用？
Oracle在恢复数据的过程中，当恢复某个表时，如果该表已经存在，就要根据ignore参数的设置来决定如何操作。若ignore=Y，Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，例如主键约束，那么出错的记录不会插入，但合法的记录会添加到表中。如果ignore=N，那么Oracle不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续恢复下一个表，默认值为N。
3. 用法
- 导入方案
权限：imp_full_database - 导入自身的方案
imp userid=scott/tiger file=’/tmp/xxx.dmp’ - 导入其它方案
imp userid=system/manager file=’/tmp/xxx.dmp’ log=’/tmp/mylog.log’ fromuser=system touser=scott - 导入表
imp userid=scott/tiger@orcl tables=(emp) file=’/tmp/xx.dmp’ - 导入表到其它用户
imp userid=system/system@orcl tables=(emp) file=’/tmp/xx.dmp’ touser=scott - 导入表的结构
imp userid=soctt/tiger@orcl tables=(emp) file=’/tmp/xx.dmp’ rows=n - 导入数据
imp userid=soctt/tiger@orcl tables=(emp) file=’/tmp/xx.dmp’ ignore=y
三、 expdp参数使用
1. 参数详解
数据泵导出实用程序提供了一种用于在 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=job_name。
COMPRESSION
减少转储文件大小。
有效的关键字值为: ALL, DATA_ONLY, [METADATA_ONLY] 和 NONE。
CONTENT
指定要卸载的数据。
有效的关键字值为: [ALL], DATA_ONLY 和 METADATA_ONLY。
DATA_OPTIONS
数据层选项标记。
有效的关键字值为: XML_CLOBS。
DIRECTORY
用于转储文件和日志文件的目录对象。
DUMPFILE
指定目标转储文件名的列表 [expdat.dmp]。
例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
ENCRYPTION
加密某个转储文件的一部分或全部。
有效的关键字值为: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY 和 NONE
。
ENCRYPTION_ALGORITHM
指定加密的方式。
有效的关键字值为: [AES128], AES192 和 AES256。
ENCRYPTION_MODE
生成加密密钥的方法。
有效的关键字值为: DUAL, PASSWORD 和 [TRANSPARENT]。
ENCRYPTION_PASSWORD
用于在转储文件中创建加密数据的口令密钥。
ESTIMATE
计算作业估计值。
有效的关键字值为: [BLOCKS] 和 STATISTICS。
ESTIMATE_ONLY
计算作业估计值而不执行导出。
EXCLUDE
排除特定对象类型。
例如, EXCLUDE=SCHEMA:"='HR'"。
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
用于导出表的子集的谓词子句。
例如, QUERY=employees:"WHERE department_id > 10"。
REMAP_DATA
指定数据转换函数。
例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
REUSE_DUMPFILES
覆盖目标转储文件 (如果文件存在) [N]。
SAMPLE
要导出的数据的百分比。
SCHEMAS
要导出的方案的列表 [登录方案]。
SOURCE_EDITION
用于提取元数据的版本。
STATUS
监视作业状态的频率, 其中
默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
TABLES
标识要导出的表的列表。
例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
TABLESPACES
标识要导出的表空间的列表。
TRANSPORTABLE
指定是否可以使用可传输方法。
有效的关键字值为: ALWAYS 和 [NEVER]。
TRANSPORT_FULL_CHECK
验证所有表的存储段 [N]。
TRANSPORT_TABLESPACES
要从中卸载元数据的表空间的列表。
VERSION
要导出的对象版本。
有效的关键字值为: [COMPATIBLE], LATEST 或任何有效的数据库版本。
------------------------------------------------------------------------------
下列命令在交互模式下有效。
注: 允许使用缩写。
ADD_FILE
将转储文件添加到转储文件集。
CONTINUE_CLIENT
返回到事件记录模式。如果处于空闲状态, 将重新启动作业。
EXIT_CLIENT
退出客户机会话并使作业保持运行状态。
FILESIZE
用于后续 ADD_FILE 命令的默认文件大小 (字节)。
HELP
汇总交互命令。
KILL_JOB
分离并删除作业。
PARALLEL
更改当前作业的活动 worker 的数量。
REUSE_DUMPFILES
覆盖目标转储文件 (如果文件存在) [N]。
START_JOB
启动或恢复当前作业。
有效的关键字值为: SKIP_CURRENT。
STATUS
监视作业状态的频率, 其中
默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
STOP_JOB
按顺序关闭作业执行并退出客户机。
有效的关键字值为: IMMEDIATE。
| Original Export Parameter | Comparable Data Pump Export Parameter |
|---|---|
BUFFER |
A parameter comparable to is not needed.BUFFER |
COMPRESS |
A parameter comparable to is not needed.COMPRESS |
CONSISTENT |
A parameter comparable to is not needed. Use and for this functionality.CONSISTENT``FLASHBACK_SCN``FLASHBACK_TIME |
CONSTRAINTS |
EXCLUDE=CONSTRAINT |
DIRECT |
A parameter comparable to is not needed. Data Pump Export automatically chooses the best method (direct path mode or external tables mode).DIRECT |
FEEDBACK |
STATUS |
FILE |
DUMPFILE |
FILESIZE |
FILESIZE |
FLASHBACK_SCN |
FLASHBACK_SCN |
FLASHBACK_TIME |
FLASHBACK_TIME |
FULL |
FULL |
GRANTS |
EXCLUDE=GRANT |
HELP |
HELP |
INDEXES |
EXCLUDE=INDEX |
LOG |
LOGFILE |
OBJECT_CONSISTENT |
A parameter comparable to is not needed.OBJECT_CONSISTENT |
OWNER |
SCHEMAS |
PARFILE |
PARFILE |
QUERY |
QUERY |
RECORDLENGTH |
A parameter comparable to is not needed because sizing is done automatically.RECORDLENGTH |
RESUMABLE |
A parameter comparable to is not needed. This functionality is automatically provided for users who have been granted the role.RESUMABLE``EXP_FULL_DATABASE |
RESUMABLE_NAME |
A parameter comparable to is not needed. This functionality is automatically provided for users who have been granted the role.RESUMABLE_NAME``EXP_FULL_DATABASE |
RESUMABLE_TIMEOUT |
A parameter comparable to is not needed. This functionality is automatically provided for users who have been granted the role.RESUMABLE_TIMEOUT``EXP_FULL_DATABASE |
ROWS=N |
CONTENT=METADATA_ONLY |
ROWS=Y |
CONTENT=ALL |
STATISTICS |
A parameter comparable to is not needed. Statistics are always saved for tables.STATISTICS |
TABLES |
TABLES |
TABLESPACES |
TABLESPACES (Same parameter; slightly different behavior) |
TRANSPORT_TABLESPACE |
TRANSPORT_TABLESPACES (Same parameter; slightly different behavior) |
TRIGGERS |
EXCLUDE=TRIGGER |
TTS_FULL_CHECK |
TRANSPORT_FULL_CHECK |
USERID |
A parameter comparable to is not needed. This information is supplied as the username and password when you invoke Export.USERID |
VOLSIZE |
A parameter comparable to is not needed.VOLSIZE |
2. 导出步骤
- 创建DIRECTORY
create directory dir_dp as 'D:/oracle/dir_dp';
查看逻辑目录是否创建成功
sql>select * from dba_directories
- 授权
Grant read,write on directory dir_dp to zftang;
--查看目录及权限
SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d
WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;
- 执行导出
expdp zftang/zftang@fgisdb schemas=zftang directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1
With the Partitioning, OLAP and Data Mining options
启动 "ZFTANG"."SYS_EXPORT_SCHEMA_01": zftang/********@fgisdb sch
ory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log; */
备注：
1、directory=dir_dp必须放在前面，如果将其放置最后，会提示
ORA-39002: 操作无效
ORA-39070: 无法打开日志文件。
ORA-39087: 目录名 DATA_PUMP_DIR; 无效
2、在导出过程中，DATA DUMP 创建并使用了一个名为SYS_EXPORT_SCHEMA_01的对象，此对象就是DATA DUMP导出过程中所用的JOB名字，如果在执行这个命令时如果没有指定导出的JOB名字那么就会产生一个默认的JOB名字，如果在导出过程中指定JOB名字就为以指定名字出现
如下改成：
expdp zftang/zftang@fgisdb schemas=zftang directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;
3、导出语句后面不要有分号，否则如上的导出语句中的job表名为‘my_job1;’，而不是my_job1。因此导致expdp zftang/zftang attach=zftang.my_job1执行该命令时一直提示找不到job表
用法及解释：
expdp 用户名/密码@ip地址/实例 [属性]
ip地址不写默认就是本地
userid=test/test --导出的用户，本地用户!!
directory=dmpfile --导出的逻辑目录，一定要在oracle中创建完成的，并且给用户授权读写权限
dumpfile=xx.dmp --导出的数据文件的名称，如果想在指定的位置的话可以写成dumpfile=/home/oracle/userxx.dmp
logfile=xx.log --日志文件，如果不写这个参数的话默认名称就是export.log，可以在本地的文件夹中找到
schemas=userxx --使用dblink导出的用户不是本地的用户，需要加上schema来确定导出的用户，类似于exp中的owner，但还有一定的区别
EXCLUDE=TABLE:"IN('T1','T2','T3')" --exclude 可以指定不导出的东西，table，index等，后面加上不想导出的表名
network_link=db_local --这个参数是使用的dblink来远程导出，需要指定dblink的名称
3. 数据泵导出的各种模式
-
按表模式导出：
expdp zftang/zftang@fgisdb tables=zftang.bi_manhole_info dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job -
按查询条件导出：
expdp zftang/zftang@fgisdb tables=zftang.b$i_exch_info dumpfile =expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query=’“where rownum<11”’ -
按表空间导出：
Expdp zftang/zftang@fgisdb dumpfile=expdp_tablespace.dmp tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job -
导出方案：
Expdp zftang/zftang DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=zftang,gwm -
导出整个数据库：
expdp zftang/zftang@fgisdb dumpfile =full.dmp full=y logfile=full.log directory=dir_dp job_name=my_job
四、 impdp参数使用
1. 参数详解
数据泵导入实用程序提供了一种用于在 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=job_name。
CONTENT 指定要加载的数据。有效的关键字为: [ALL], DATA_ONLY 和 METADATA_ONLY。
DATA_OPTIONS 数据层选项标记。有效的关键字为: SKIP_CONSTRAINT_ERRORS。
DIRECTORY 用于转储文件, 日志文件和 SQL 文件的目录对象。
DUMPFILE 从中导入的转储文件的列表 [expdat.dmp]。例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
ENCRYPTION_PASSWORD 用于访问转储文件中的加密数据的口令密钥。 对于网络导入作业无效。
ESTIMATE 计算作业估计值。 有效的关键字为: [BLOCKS] 和 STATISTICS。
EXCLUDE 排除特定对象类型。 例如, EXCLUDE=SCHEMA:"='HR'"。
FLASHBACK_SCN 用于重置会话快照的 SCN。
FLASHBACK_TIME 用于查找最接近的相应 SCN 值的时间。
FULL 导入源中的所有对象 [Y]。
HELP 显示帮助消息 [N]。
INCLUDE 包括特定对象类型。 例如, INCLUDE=TABLE_DATA。
JOB_NAME 要创建的导入作业的名称。
LOGFILE 日志文件名 [import.log]。
NETWORK_LINK 源系统的远程数据库链接的名称。
NOLOGFILE 不写入日志文件 [N]。
PARALLEL 更改当前作业的活动 worker 的数量。
PARFILE 指定参数文件。
PARTITION_OPTIONS 指定应如何转换分区。 有效的关键字为: DEPARTITION, MERGE 和 [NONE]。
QUERY 用于导入表的子集的谓词子句。例如, QUERY=employees:"WHERE department_id > 10"。
REMAP_DATA 指定数据转换函数。 例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
REMAP_DATAFILE 在所有 DDL 语句中重新定义数据文件引用。
REMAP_SCHEMA 将一个方案中的对象加载到另一个方案。
REMAP_TABLE 将表名重新映射到另一个表。 例如, REMAP_TABLE=EMP.EMPNO:REMAPPKG.EMPNO。
REMAP_TABLESPACE 将表空间对象重新映射到另一个表空间。
REUSE_DATAFILES 如果表空间已存在, 则将其初始化 [N]。
SCHEMAS 要导入的方案的列表。
SKIP_UNUSABLE_INDEXES 跳过设置为“索引不可用”状态的索引。
SOURCE_EDITION 用于提取元数据的版本。
SQLFILE 将所有的 SQL DDL 写入指定的文件。
STATUS 监视作业状态的频率, 其中默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
STREAMS_CONFIGURATION 启用流元数据的加载
TABLE_EXISTS_ACTION 导入对象已存在时执行的操作。有效的关键字为: APPEND, REPLACE, [SKIP] 和 TRUNCATE。
TABLES 标识要导入的表的列表。 例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
TABLESPACES 标识要导入的表空间的列表。
TARGET_EDITION 用于加载元数据的版本。
TRANSFORM 要应用于适用对象的元数据转换。有效的关键字为: OID, PCTSPACE, SEGMENT_ATTRIBUTES 和 STORAGE。
TRANSPORTABLE 用于选择可传输数据移动的选项。 有效的关键字为: ALWAYS 和 [NEVER]。 仅在 NETWORK_LINK 模式导入操作中有效。
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 的数量。
START_JOB 启动或恢复当前作业。 有效的关键字为: SKIP_CURRENT。
STATUS 监视作业状态的频率, 其中默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
STOP_JOB 按顺序关闭作业执行并退出客户机。有效的关键字为: IMMEDIATE。
2. 导入到指定用户(模式)
由于是导入到远程库，所以还需要创建下转储文件所在目录。
-- 省略掉如何把导出的转储文件移动到指定远程库目录下
SQL> create directory dump_dir as '/home/oracle/oradir';
Directory created.
SQL>
-- 导入数据
-- impdp 用户名/密码 DIRECTORY=转储文件所在目录 DUMPFILE=带导入的转储文件名 SCHEMAS=待导入的用户(模式)名
impdp sys/orcl DIRECTORY= dump_dir DUMPFILE=schema_crxj_collect.dmp SCHEMAS=CRXJ_COLLECT;
因为不要求导出和导入的是同一个用户，所以数据泵这个工具可以方便的在不同用户（模式）见传到数据对象。
数据泵高级操作中可以实现数据对象的逻辑恢复，包括按表、按用户模式（本文即是），按表空间和全库导入导出。
五、 导入导出操作注意事项
使用EXPDP和IMPDP时应该注意的事项：
-
EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。
-
EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。
-
IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。
-
expdp或impdp命令时，可暂不指出用户名/密码@实例名 as 身份，然后根据提示再输入，如：
expdp schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
一、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以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
windows在cmd下执行
用oracle的exp工具导出的dmp文件也包含了字符集信息，dmp文件的第2和第3个字节记录了dmp文件的字符集。
如果dmp文件不大，比如只有几M或几十M，可以用UltraEdit打开(16进制方式)，看第2第3个字节的内容，如0354，
然后用以下SQL查出它对应的字符集:
SQL> select nls_charset_name(to_number('0354','xxxx')) from
dual;
ZHS16GBK
如果dmp文件很大，比如有2G以上(这也是最常见的情况)，用文本编辑器打开很慢或者完全打不开，可以用以下命令(在unix主机上):
cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
然后用上述SQL也可以得到它对应的字符集。
在exp/imp时确实要注意字符集。我们说说最能成功的一条路。 1)确定源库和目标库数据库字符集一致。
2)确定导出时，环境变量字符集和数据库兼容
3)确定导入时，环境变量字符集和数据库兼容。这三点对了，就没问题了。
导入 导出做日志是好习惯
exp owner=<> file=<.dmp> log=<.log> 这三个参数作为最小集合记住吧
expdp是服务器工具，只要注意数据库字符集就可以了。
exp是客户端工具，系统在导出时使用的是客户端的环境变量定义的字符集。
可以在客户端使用expdp，但执行和结果都在服务器。
expdp是服务器工具，只要注意数据库字符集就可以了。
exp是客户端工具，系统在导出时使用的是客户端的环境变量定义的字符集。
六、 其他考虑
1. 使用数据泵的限制性因素
- 空间
- 网络端口：1521或22
- 网络带宽
2. Oracle高低版本间如何迁移
（1） 示例
源数据库：RHEL + Oracle 11.2.0.3
目标数据库：HP-UX + Oracle 10.2.0.4
需求：迁移部分表 11.2.0.3–>10.2.0.4，若迁移范围内的有些表在目标库已经存在，则替换。
本次迁移数据量<1G.
初定方案：低版本的客户端连接到高版本数据库，用低版本导出，低版本导入。
- 采用初定方案，目标数据库所在服务器连接到源数据库，exp导出过程中报错。
ZJCRNOPDB 36: sqlplus -version
SQL*Plus: Release 10.2.0.4.0 - Production
ZJCRNOPDB 37: more mod_homepage.sh
exp crnop/password@zjtestdb parfile=/usr3/orabak2/par_mod_homepage
ZJCRNOPDB 38: more par_mod_homepage
file=/usr3/orabak2/mod_homepage.dmp
log=/usr3/orabak2/mod_homepage.log
tables=
……,
MOD_HOMEPAGE_ALARM_STAT_INFO
ZJCRNOPDB 39: ./mod_homepage.sh
Export: Release 10.2.0.4.0 - Production on 星期三 5月 21 13:42:25 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 MOD_HOMEPAGE_BTS_CHECK_DETAIL导出了 3502523 行
……
. . 正在导出表 MOD_HOMEPAGE_NOTICE_TYPE导出了 7 行
EXP-00008: 遇到 ORACLE 错误 1455
ORA-01455: 转换列溢出整数数据类型
EXP-00000: 导出终止失败
ZJCRNOPDB 40:
遭遇ORA-01455，证明初定方案有问题，低版本客户端不能导出高版本数据。需要更换方案。
- 采用expdp/impdp迁移方案
- 源数据库服务器上expdp 导出加上参数 version=10.2.0.4.0
[oracle@zjoradb jy]$ more par_mod_homepage
directory=jy
dumpfile=mod_homepage.dmp
logfile=mod_homepage.log
version=10.2.0.4.0
tables=
……,
……,
MOD_HOMEPAGE_ALARM_STAT_INFO
[oracle@zjoradb jy]$ more expdp_mode_homepage.sh
expdp crnop/password parfile=par_mod_homepage
directory=jy库中已经存在，不用再建立。直接运行导出脚本导出数据。
[oracle@zjoradb jy]$ ./expdp_mode_homepage.sh
Export: Release 11.2.0.3.0 - Production on Wed May 21 14:19:44 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "CRNOP"."SYS_EXPORT_TABLE_08": crnop/******** parfile=par_mod_homepage
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 437.3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/STATISTICS/TABLE_STATISTICS
. . exported "CRNOP"."MOD_HOMEPAGE_BTS_CHECK_DETAIL" 291.5 MB 3502523 rows
……
Master table "CRNOP"."SYS_EXPORT_TABLE_08" successfully loaded/unloaded
******************************************************************************
Dump file set for CRNOP.SYS_EXPORT_TABLE_08 is:
/home/oracle/jy/mod_homepage.dmp
Job "CRNOP"."SYS_EXPORT_TABLE_08" successfully completed at 14:20:19
[oracle@zjoradb jy]$
- 在目标数据库impdp导入，由于需求是替换已存在的表，所以加入下面这个参数：
TABLE_EXISTS_ACTION=replace
TABLE_EXISTS_ACTION
导入对象已存在时执行的操作。
有效的关键字为: APPEND, REPLACE, [SKIP] 和 TRUNCATE。
创建directory，然后将要导入的dmp文件cp到此目录。
SQL> create or replace directory jy as '/usr3/orabak2/';
ZJCRNOPDB 65: more impdp*
impdp crnop/password directory=jy dumpfile=mod_homepage.dmp logfile=impdp_mod_homepage.log TABLE_EXISTS_ACTION=replace
ZJCRNOPDB 74: ./impdp_mod_homepage.sh
Import: Release 10.2.0.4.0 - 64bit Production on 星期三, 21 5月, 2014 14:39:29
Copyright (c) 2003, 2007, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "CRNOP"."SYS_IMPORT_FULL_01"
启动 "CRNOP"."SYS_IMPORT_FULL_01": crnop/******** directory=jy dumpfile=mod_homepage.dmp logfile=impdp_mod_homepage.log TABLE_EXISTS_ACTION=replace
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "CRNOP"."MOD_HOMEPAGE_BTS_CHECK_DETAIL" 291.5 MB 3502523 行
……
处理对象类型 TABLE_EXPORT/TABLE/COMMENT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-31684: 对象类型 INDEX:"CRNOP"."PK_MOD_WF_TOPN_PROBLEM" 已存在
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: 对象类型 CONSTRAINT:"CRNOP"."PK_MOD_WF_TOPN_PROBLEM" 已存在
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: 跳过从属对象类型 INDEX_STATISTICS, 基本对象类型 INDEX:"CRNOP"."PK_MOD_WF_TOPN_PROBLEM" 已存在
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "CRNOP"."SYS_IMPORT_FULL_01" 已经完成, 但是有 3 个错误 (于 14:46:54 完成)
导入成功，上面的错误是索引的不规范命名导致索引对象重名的，改名重建下那个索引即可，与本案例不相关。
- 总结：高版本->低版本迁移数据，迁移场景是11g->10g，建议采用EXPDP/IMPDP方式替换传统exp/imp方式，EXPDP从高版本导出时要加入参数version=10g版本号。
（2）规则
Oracle数据中IMP/EXP工具可用于对数据进行迁移。IMP命令用于把Dmp文件从本地导入到远程数据库服务器，而EXP命令则是把数据从远程数据库服务器导出到本地的Dmp文件。其功能相当于Oracle数据库的备份与还原。利用IMP/EXP工具可以轻松的实现对数据库的迁移工作，甚至是跨平台的数据库迁移。
exp/imp：
这也算是最常用最简单的方法了，一般是基于应用的owner级做导出导入。
操作方法为：在新库建立好owner和表空间，停老库的应用，在老库做exp user/pwd owner=XXX file=exp_xxx.dmp log=exp_xxx.log buffer=6000000，传dmp文件到新库，在新库做imp user/pwd fromuser=XXX touser=XXX file=exp_xxx.dmp log=imp_xxx.log ignore=y.
优缺点：优点是可以跨平台使用；缺点是停机时间长，停机时间为从exp到网络传输到新库，再加上imp的时间。
Oracle的imp/exp组件是我们常用的工具，它的一个操作原则就是向下兼容。下面是据此总结的几个使用规则和相关测试：
规则1：低版本的exp/imp可以连接到高版本（或同版本）的数据库服务器，但高版本的exp/imp不能连接到低版本的数据库服务器
规则2：高版本exp出的dmp文件，低版本无法imp（无法识别dmp文件）
规则3：低版本exp出的dmp文件，高版本可以imp（向下兼容）
规则4：从Oracle 低版本的Export数据可以Import到Oracle高版本中，但限于Oracle的相邻版本
3. public对象dblink、同义词在迁移时如何处理的
创建dblink必须使用dblink的所有者登录数据库创建，才能创建到当前的模式下。且不知道数据库用户密码的情况下，将无法直接创建dblink。
那么使用expdp方式迁移dbkink将是一种非常方便简单的方法。
可以使用如下方法将整个数据库的dblink保存到一个转储文件中：
expdp dumpfile=dblink.dmp directory=test full=y include=db_link userid="/ as sysdba"
如果想导出某几个模式下的dblink：
expdp dumpfile=dblink.dmp directory=test schemas=user1,… include=db_link userid="/ as sysdba"
导出公共dblink：
expdp dumpfile=dblink.dmp directory=test full=y include=db_link:“IN (select db_link from dba_db_links where owner=‘PUBLIC’)” userid="/ as sysdba"
oracle中有时在库间迁移时，按schema导出时不会导出public对象，可以通过数据泵expdp和impdp的方式进行迁移。
- 创建public dblink
create public database link test
connect to r_test IDENTIFIED BY test123
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb11g)
)
)';
测试dblink连通性
select * from dual@test;
--DUMMY
--X
- 创建导出目录
CREATE DIRECTORY test AS '/oracle/dump_dir';
- 按dblink名称导出dblink,注意命令行的字符转义，如果不想用转义可以通过parfile方式导
expdp r_test/test123 DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=expdp_public_dblink.log full=y include=DB_LINK:\"IN \'TEST\'\'TEST\'\"
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "R_TEST"."SYS_EXPORT_FULL_01": r_test/******** DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=expdp_public_dblink.log full=y include=DB_LINK:"IN ('TEST')"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "R_TEST"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for R_TEST.SYS_EXPORT_FULL_01 is:
/oracle/dump_dir/expdp_public_dblink.dmp
Job "R_TEST"."SYS_EXPORT_FULL_01" successfully completed at Mon Jul 15 17:09:49 2019 elapsed 0 00:00:08
- 删除dblink
drop public database link test;
- 导入
impdp r_test/test123 DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=impdp_public_dblink.log
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "R_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "R_TEST"."SYS_IMPORT_FULL_01": r_test/******** DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=impdp_public_dblink.log
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "R_TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 15 17:12:20 2019 elapsed 0 00:00:01
- 检查导入结果，检查dblink连通性
select * from dba_db_links;
select * from dual@test;
--DUMMY
--X
测试成功，可以通过expdp和impdp实现public dblink的迁移，这在不知道创建dblink的用户密码时比较有用。同理还有public synonym也可以用这种方式。
4. 数据泵导入导出如何终止
[oracle@database ~]$ expdp \"sys/oracle as sysdba\" attach=SYS_EXPORT_SCHEMA_02
Export: Release 11.2.0.4.0 - Production on Mon Jan 26 10:31:42 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_02
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 0D85DF9D2F014494E053A80013AC483D
Start Time: Monday, 26 January, 2015 10:27:03
Mode: SCHEMA
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "sys/******** AS SYSDBA" schemas=(prod,tts_fnd,tts_cms,tts_hr,tts_ecm,tts_coa,tts_exm,tts_htm) dumpfile=tt.dmp logfile=tt.log directory=test
State: EXECUTING
Bytes Processed: 17,595,108,512
Percent Done: 81
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/backup/tt.dmp
bytes written: 17,610,469,376
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: TTS_EXM
Object Name: EXM_PERSON_ITEMS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 80
Total Objects: 1,400
Worker Parallelism: 1
Export> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): yes
[oracle@database ~]$
5. 逻辑迁移时数据库对job是如何处理的
userid="/ as sysdba"
directory=EXP_DIR
dumpfile=expdp_job.dmp
logfile=expdp_job.log
include=job
6. 数据校验
数据泵的数据校验整体思路：
- 导出和导入日志的记录，会记录对象总大小和行数
- 传输时dmp文件的大小比较
- 进库查询数据行数，对象的个数
- 采用dblink的方式，进行权限对象比较
--失效对象检查
select 'alter ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME ||
' compile' || ';' cmd
from dba_objects
where status = 'INVALID'
and object_type != 'PACKAGE BODY'
and object_type <>'VIEW'
order by object_type;
41.--角色、权限对比
select grantee,granted_role from dba_role_privs
minus
select grantee,granted_role from dba_role_privs@target_db;
--角色对比
select role from dba_roles
minus
select role from dba_roles@target_db;
--系统权限对比
select grantee,PRIVILEGE from dba_sys_privs
minus
select grantee,PRIVILEGE from dba_sys_privs@target_db;
--修复系统丢失权限
set lines 200 pages 200
select 'grant '||privilege||' to '||grantee||';'
from (select grantee,PRIVILEGE from dba_sys_privs
minus select grantee,PRIVILEGE from dba_sys_privs@target_db
) ;
--对象权限检查
select owner,table_name,grantee,PRIVILEGE from dba_tab_privs
where grantee not in ('PUBLIC','SYS','SYSMAN','DBA','SYSTEM','GOLDENGATE') and table_name not like '%BIN%'
minus
select owner,table_name,grantee,PRIVILEGE from dba_tab_privs@target_db
where grantee not in ('PUBLIC','SYS','SYSMAN','DBA','SYSTEM','GOLDENGATE') and table_name not like '%BIN%';
--修复对象权限
set lines 200 pages 200
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from (select owner,table_name,grantee,PRIVILEGE from dba_tab_privs
where owner not in ('PUBLIC','SYSMAN','DBA','SYSTEM','GOLDENGATE') and table_name not like '%BIN%' and grantee not in ('SYSMAN','SYSTEM','DBA','PUBLIC','GOLDENGATE','XDB','WMSYS','MDSYS','ORDSYS','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE')
minus
select owner,table_name,grantee,PRIVILEGE from dba_tab_privs@target_db
) ;
--db link对比、已重建
select owner,db_link from dba_db_links
minus
select owner,db_link from dba_db_links@target_db;
--job 对比
select job,log_user,what from dba_jobs
minus
select job,log_user,what from dba_jobs@target_db;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
