EXP & IMP

李志强OracleLeave a comment

导入导出时 oracle 提供的实用工具, 如果这些被导出的对象还存在其他的相关对象, 比如要被导出的表上还存在索引, 注释等, 则导出工具会自动将这些相关的对象也提取出来, 并放入到导出的文件中去. 看来是将对象一起导出, 而非需要先在目标数据库中创建对象, 然后再将数据导入.

可以实现跨平台.

相对于逻辑备份来说, 导入导出是将数据库中的对象与数据存储在一个文件中, 然后在导入到新的数据库, 这是数据库中逻辑文件的转储, 而物理备份, 备份的是数据所在的数据块.

在执行导入过程中, 按照下面的顺序导入表, 表里的数据以及相关对象:

1. 创建表结构

2. 导入表的数据

3. 创建相关索引

4. 导入触发器

5. 对导入的表启用完整性约束

6. 建立所有位图, 函数以及其他索引

以上, 都是导入时, 自动进行的. 有时, 我们导入时, 在目标数据库中已经存在了该表, 因为创建表结构在第一步, 所以导致后边的操作都没有进行, 这时我们可以使用ignor=y 这个参数, 这样, 我们就可以把导出文件中包含的数据累加到该表中去.

10g 以后数据泵

oracle 10g 以后推出了数据泵(data pump), 与之前的导入导出工具类似, 但是他们之间最大的区别是, 数据泵只能在服务器端运行. 而不能将该转储文件导出到客户端.

使用数据泵的优点:

  • 如果转储过程中出现问题, 可以实现续传

  • 可以只处理某些对象, 或者不处理某些对象, 或者只处理某些对象中的满足指定条件的数据 等

  • 在不实际执行导出的情况下, 估计整个导出工作需要占用多少磁盘空间

  • 通过 db link, 将远程数据库导出到转储文件

  • 通过 db link, 直接将远程的, 位于其他主机上的数据库里的数据导入到当前数据库中, 从而实现跨平台的数据迁移.

  • 在导入时, 可以修改导入数据所在的schema名称, 表空间名称以及数据文件名称

  • 通过采样, 导出部分数据

  • 只导出元数据(比如表结构 等), 而不导出实际的数据, 而且在导出元数据的过程中, 还可以指定是否要启用压缩功能.

  • 可以进行并行操作.

数据泵在数据导出到服务器端时, 可以通过目录对象(dirctory object)来控制生成的转储文件应该放在服务器端的那个目录下. 目录对象时数据库中的一种对象, 代表服务器文件系统上的目录结构, 通过在数据库中使用这些目录对象, 就可以在数据库中, 对某个指定目录下的文件进行读取和写入操作. 我们就不需要对文件系统路径进行硬编码.

在数据泵运行过程中, 主表(Master Table 简称MT)起到了关键性作用, 在MT表中, 保存了整个数据泵运行过程中的相关信息, 这些信息包括要处理的所有对象的信息, 当前正在处理的对象信息. 在启动数据泵的时候, 如果我们制定了任务名称, 则 MT 表名称就等于任务名称.

创建目录对象: create directory my_dir as ‘/opt/mydir_exp’;

授权用户读写该目录对象: grant write on directory my_dir to hr;

演示:

在执行导出时, expdp 先创建 MT 表, 并将对象信息插入 MT 表以后, 开始执行实际的导出任务, 当所有对象都导出以后, 将 MT 表也一起导出到转储文件中, 等到导出任务成功完成或通过命令删除了导出任务时, MT 表会自动被删除. 如果导出任务异常终止, MT 表会保留.

在执行导入时, impdb 先将 MT 表从转储文件中读出, 并写入目标数据库, 然后读取 MT 表中所记录的对象信息, 并根据读取出来的对象名称, 将这些对象从转储文件中提取出来, 并插入到目标数据库里.

使用命令行方式, 类似于 exp/imp,

expdp help=y  查看导出时的参数

impdp help=y 查看导入时的参数.

例如:

 

EXPDP

~ 导出某个指定的表

expdp hr/hr directory=my_dir dumpfile=exp%U.dmp tables=(employees) job_name=EXP

~ 导出某几个用户

expdp system/oracle directory=my_dir dumpfile=exp%U.dmp schemas=(hr, oe)

~ 导出整个数据库

expdp system/oracle directory=my_dir dumpfile=exp%U.dmp full=y parallel=4

一些高级应用:

~ 指定不导出某些对象

expdp system/oracle directory=my_dir dumpfile=exp%U.dmp parfile=exp_par.txt   其中, exp_par.txt 是我们之前先编辑好的参数文件.

vi exp_par.txt 如下:

exclude=table:”like ‘EMPLOYEES%’”   — 表名大小写敏感. table 后边的内容类似sql 语句中的 where 条件, 在比如下边的例子:

schemas=hr

exclude=table: “in (‘EMPLOYEES’, ‘DEPARTMENTS’)”

schemas=hr

~ 只导出指定类型的对象

expdp system/oracle directory=my_dir dumpfile=hr_oe.dmp parfile=exp_par.txt

其中, exp_par.txt

include = function

include = procedure

include = package

include = view:”like ‘PRODUCT%’”

schemas = hr, oe

~ 只导出符合条件的数据

expdp hr/hr directory=my_dir dumpfile=hr.dmp parfile=exp_part.txt

其中, exp_part.txt 为:

tables=(employees)

query=employees:“where department_id = 40 order by employee_id”

~ 对数据库中的数据进行采样以后, 导出采样数据

expdp hr/hr directory=my_dir dumpfile=exp%U.dmp schemas=hr sample=30

expdp hr/hr directory=my_dir dumpfile=exp%U.dmp schemas=hr sample=employees:30  –只是 employees 30%采样导出, 其他的表都正常导出

 
中断导出任务以后, 从中断处再次启动导出任务

expdp hr/hr attach=EXP  — 这个attach=EXP, 表示继续EXP这个任务. 这样就进入了任务的交互式界面,

help 可以看到在交互界面中的命令, 我们可以为任务增加或编辑些内容, 比如 add_file=40M, 等, 编辑完后, start_job 任务就开始继续执行, 但是我们看不到,是在后台执行, 我们可以使用continue_client 该命令可以将任务在后台执行的进度体现到前台.

 

IMPDP

常用的参数:

remap_datafile: 用户不同文件系统平台, 转换文件路径

remap_tablespace: 用于将对象从一个表空间导入到另一个表空间 比如: remap_tablespace=users:example 说明导出文件对象位于users表空间, 导入以后, 对象位于example表空间

remap_schema: 用于将对象从一个用户下导入到另一个用户下. 比如: remap_schema=hr:hjs 说明导出文件对象位于 hr用户下, 导入以后, 对象位于 hjs用户下.

~ 导入某个指定的表

impdp hjs/hjs directory=my_dir dumpfile=exp01.dmp table=(emp) remap_schema=hr:hsj remp_tablespace=users:example

~ 导入某几个用户

impdp system/oracle directory=my_dir dumpfile=exp01.dmp remap_schema=hr:hsj   将 hr 下的所有对象都导入 hsj.

~ 导入整个数据库

impdp system/oracle directory=my_dir dumpfile=exp%U.dmp full=y

高级方法与 expdp 类似

比如:

impdp system/oracle directory=my_dir dumpfile=exp01.dmp parfile=imp_par.txt

其中 imp_par.txt 的内容为:

exclude=table: “in (‘EMPLOYEES’, ‘DEPARTMENTS’)”

remap_schema=hr:hjs

 

基本语法和实例：

1、EXP:


有三种主要的方式（完全、用户、表） 
1、完全： 
exp icdmain/icd rows=y indexes=n compress=n buffer=65536 feedback=100000 full=y file=exp_.dmp log=exp.log

如果要执行完全导出，必须具有特殊的权限 
2、用户模式： 
exp icdmain/icd owner=icdmain rows=y indexes=n compress=n buffer=65536 feedback=100000 file=exp.dmp log=exp.log 
3、表模式： 
exp icdmain/icd rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 file=exp.dmp log=exp.log tables=tab1,tab2,tab3

exp help=y  可以用来查看相关的参数配置

 

2、IMP:


具有三种模式（完全、用户、表）与 EXP 对应  
1、完全： 
IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:FULL.DMP FULL=Y 
2、用户模式： 
IMP SONIC/SONIC BUFFER=64000 FILE=C:SONIC.DMP FROMUSER=SONIC TOUSER=SONIC 
这样用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据。 
3、表模式：  
IMP SONIC/SONIC BUFFER=64000 FILE=C:SONIC.DMP OWNER=SONIC TABLES=(SONIC) 
这样用户SONIC的表SONIC就被导入。

imp help=y 可以用来查看相关参数配置

 

示例

4.1 oracle创建表空间，创建用户

//创建临时表空间

create temporary tablespace test_temp

tempfile ‘/u01/app/oracle/oradata/orcl/test_temp01.Dbf'

size 32m autoextend on next 32m maxsize 2048m

extent management local;

//创建数据表空间

create tablespace test_data logging

datafile '/u01/app/oracle/oradata/orcl/test_data01.dbf' ­

size 32m autoextend on

next 32m maxsize 2048m

extent management local;

//创建用户并指定表空间

create user username identified by password

default tablespace test_data

temporary tablespace test_temp;

//给用户授予权限 ­

grant connect,resource to username;

先创建一个用户和表空间，用户名david，密码david.在这个表空间下创建一个表：tianle。随便插入些数据。代码如下：

SQL> create tablespace test_data

3 datafile '/u01/app/oracle/oradata/orcl/test_data01.dbf'

4 size 5m;

Tablespace created.

SQL> create user david identified by david default tablespace test_data;

SQL> grant connect,resource to david;

SQL> conn david/david

SQL> create table tianle(id number, content varchar2(100));

SQL> set wrap off

SQL> column id format a20;

SQL> column content format a50;

4.2 表模式备份 与 恢复

备份：

[oracle@roy orcl]$ exp david/david rows=y indexes=n compress=n buffer=65536 file=exp_tianle_090101.dmp log=exp_tianle_090101.log tables=(tianle);

恢复：

[oracle@roy orcl]$ imp david/david fromuser=david touser=david rows=y indexes=n commit=y buffer=65536 file=exp_tianle_090101.dmp log=imp_tianle_090101.log tables=(tianle); ­

4.3 用户模式备份与恢复

备份：

[oracle@roy orcl]$ exp david/david owner=david rows=y indexes=n compress=n buffer=65536 file=exp_david__090101.dmp log=exp_david_090101.log; ­

恢复：

[oracle@roy orcl]$ imp david/david fromuser=david touser=david rows=y indexes=n commit=y buffer=65536 file=exp_tianle_090101.dmp log=exp_tianle_090101.log; ­

4.4 完全模式备份与恢复

备份：

[oracle@roy orcl]$ exp david/david rows=y indexes=n compress=n buffer=65536 full=y file=exp_fulldatabase_090101.dmp log=exp_fulldatabase_090101.log; ­

恢复

[oracle@roy orcl]$ imp david/david rows=y indexes=n commit=y full=y ignore=y buffer=65536 file=/tmp/exp_fulldatabase_090101.dmp log=/tmp/imp.log;

EXP常用选项 
1.FULL，这个用于导出整个数据库，在ROWS=N一起使用时，可以导出整个数据库的结构。例如： 
exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y

2. 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

3.BUFFER和FEEDBACK，在导出比较多的数据时，我会考虑设置这两个参数。例如： 
exp userid=test/test file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT

4.FILE和LOG，这两个参数分别指定备份的DMP名称和LOG名称，包括文件名和目录，例子见上面。

5.COMPRESS参数不压缩导出数据的内容。用来控制导出对象的storage语句如何产生。默认值为Y，使用默认值，对象的存储语句的init extent等于当前导出对象的extent的总和。推荐使用COMPRESS＝N。

IMP常用选项 
1、FROMUSER和TOUSER，使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。例如：假设我们做exp时导出的为test的对象,现在我们想把对象导入用户： 
imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1

2、IGNORE、GRANTS和INDEXES，其中IGNORE参数将忽略表的存在，继续导入，这个对于需要调整表的存储参数时很有用，我们可以先根据实际情况用合理的存储参数建好表，然后直接导入数据。而GRANTS和INDEXES则表示是否导入授权和索引，如果想使用新的存储参数重建索引，或者为了加快到入速度，我们可以考虑将INDEXES设为N，而GRANTS一般都是Y。例如：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=N

优化EXP/IMP的方法： 
当需要exp/imp的数据量比较大时，这个过程需要的时间是比较长的，我们可以用一些方法来优化exp/imp的操作。 
exp:使用直接路径 direct=y 
oracle会避开sql语句处理引擎,直接从数据库文件中读取数据,然后写入导出文件. 
可以在导出日志中观察到: exp-00067: table xxx will be exported in conventional path 
如果没有使用直接路径,必须保证buffer参数的值足够大. 
有一些参数于direct=y不兼容,无法用直接路径导出可移动的tablespace,或者用query参数导出数据库子集. 
当导入导出的数据库运行在不同的os下时,必须保证recordlength参数的值一致

imp:通过以下几个途径优化 
1.避免磁盘排序 
将sort_area_size设置为一个较大的值,比如100M 
2.避免日志切换等待 
增加重做日志组的数量,增大日志文件大小. 
3.优化日志缓冲区 
比如将log_buffer容量扩大10倍(最大不要超过5M) 
4.使用阵列插入与提交 
commit = y 
注意:阵列方式不能处理包含LOB和LONG类型的表,对于这样的table,如果使用commit = y,每插入一行,就会执行一次提交. 
5.使用NOLOGGING方式减小重做日志大小 
在导入时指定参数indexes=n,只导入数据而忽略index,在导完数据后在通过脚本创建index,指定 NOLOGGING选项

导出/导入与字符集 
进行数据的导入导出时，我们要注意关于字符集的问题。在EXP/IMP过程中我们需要注意四个字符集的参数：导出端的客户端字符集，导出端数据库字符集，导入端的客户端字符集，导入端数据库字符集。    

导出数据库->导出客户端->导入客户端->导入数据库

所以, 我们将导出, 导入客户端的字符集, 设置成跟导出数据库一样, 那么, 只发生一次字符集转换.
我们首先需要查看这四个字符集参数。 
查看数据库的字符集的信息： 
SQL> select * from nls_database_parameters;

PARAMETER VALUE 
—————————— ——————————————————————————– 
NLS_LANGUAGE AMERICAN 
NLS_TERRITORY AMERICA 
NLS_CURRENCY $ 
NLS_ISO_CURRENCY AMERICA 
NLS_NUMERIC_CHARACTERS ., 
NLS_CHARACTERSET ZHS16GBK 
NLS_CALENDAR GREGORIAN 
NLS_DATE_FORMAT DD-MON-RR 
NLS_DATE_LANGUAGE AMERICAN 
NLS_SORT BINARY 
NLS_TIME_FORMAT HH.MI.SSXFF AM 
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM 
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM 
NLS_DUAL_CURRENCY $ 
NLS_COMP BINARY 
NLS_NCHAR_CHARACTERSET ZHS16GBK 
NLS_RDBMS_VERSION 8.1.7.4.1 
NLS_CHARACTERSET：ZHS16GBK是当前数据库的字符集。

我们再来查看客户端的字符集信息： 
客户端字符集的参数NLS_LANG=<language>_< territory >.<Clients Characterset>. 对应含义如下:
language：指定oracle消息使用的语言，日期中日和月的显示。 
Territory：指定货币和数字的格式，地区和计算星期及日期的习惯。 
Characterset：控制客户端应用程序使用的字符集。通常设置或等于客户端的代码页。或者对于unicode应用设为UTF8。 
在windows中，查询和修改NLS_LANG可在注册表中进行： 
HKEY_LOCAL_MACHINESOFTWAREOracleHOMExx 
xx指存在多个Oracle_HOME时的系统编号。

在unix中： 
$ env|grep NLS_LANG 
NLS_LANG=simplified chinese_china.ZHS16GBK 
修改可用： 
$ export NLS_LANG=AMERICAN_AMERICA.UTF8

通常在导出时最好把客户端字符集设置得和数据库端相同。当进行数据导入时，主要有以下两种情况： 
(1) 源数据库和目标数据库具有相同的字符集设置。 
这时，只需设置导出和导入端的客户端NLS_LANG等于数据库字符集即可。 
(2) 源数据库和目标数据库字符集不同。 
先将导出端客户端的NLS_LANG设置成和导出端的数据库字符集一致，导出数据，然后将导入端客户端的NLS_LANG设置成和导出端一致，导入数据，这样转换只发生在数据库端，而且只发生一次。 
这种情况下，只有当导入端数据库字符集为导出端数据库字符集的严格超集时，数据才能完全导成功，否则，可能会有数据不一致或乱码出现。

不同版本的EXP/IMP问题 
一般来说，从低版本导入到高版本问题不大，麻烦的是将高版本的数据导入到低版本中，在Oracle9i之前，不同版本Oracle之间的EXP/IMP可以通过下面的方法来解决： 
1、在高版本数据库上运行底版本的catexp.sql； 
2、使用低版本的EXP来导出高版本的数据； 
3、使用低版本的IMP将数据库导入到低版本数据库中； 
4、在高版本数据库上重新运行高版本的catexp.sql脚本。 
但在9i中，上面的方法并不能解决问题。如果直接使用低版本EXP/IMP会出现如下错误： 
EXP-00008: orACLE error %lu encountered 
orA-00904: invalid column name 
这已经是一个公布的BUG，需要等到Oracle10.0才能解决，BUG号为2261722，你可以到METALINK上去查看有关此BUG的详细信息。 
BUG归BUG，我们的工作还是要做，在没有Oracle的支持之前，我们就自己解决。在Oracle9i中执行下面的SQL重建exu81rls视图即可。 
Create or REPLACE view exu81rls 
(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy) 
AS select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname, 
decode(bitand(r.stmt_type,1), 0,'', 'Select,') 
|| decode(bitand(r.stmt_type,2), 0,'', 'Insert,') 
|| decode(bitand(r.stmt_type,4), 0,'', 'Update,') 
|| decode(bitand(r.stmt_type,8), 0,'', 'Delete,'), 
r.check_opt, r.enable_flag, 
DECODE(BITAND(r.stmt_type, 16), 0, 0, 1) 
from user$ u, obj$ o, rls$ r 
where u.user# = o.owner# 
and r.obj# = o.obj# 
and (uid = 0 or 
uid = o.owner# or 
exists ( select * from session_roles where role='Select_CATALOG_ROLE') 
) 
/ 
grant select on sys.exu81rls to public; 
/ 
可以跨版本的使用EXP/IMP，但必须正确地使用EXP和IMP的版本： 
1、总是使用IMP的版本匹配数据库的版本，如：要导入到817中，使用817的IMP工具。 
2、总是使用EXP的版本匹配两个数据库中最低的版本，如：从9201往817中导入，则使用817版本的EXP工具

full

exp 时, 使用full 将导出全部内容(全部用户的权限, 全部数据, 全部数据库结构等等), 如果是单纯的逻辑导出还好, 这里还会导出创建 表空间的脚本, 从而自动创建表空间, 所以, 使用 full 的情况:

1. 目标数据库与来源数据库在结构上完全一样, 例如一个测试数据库, 一个正在使用数据库. 这种由于路径等所有内容都完全一样, 所以, 直接exp, imp就可以了, (这种使用full是比较合适的)

2. 目标与源数据库环境不一样, 这时, 就不能随便让imp自动创建表空间, 因为创建的路径有问题, 这个时候,做法是:  – 首先查看源数据库的用户和默认表空间:    select name from v$tablespace;    select username,default_tablespace from dba_users;  – 然后, 在目标数据库上考量好, 应该对应以上用户建立哪些表空间和用户: (注意: 导入时, 不要带索引, 要重建索引)    创建对应用户, 并给用户赋予默认表空间    给予这些新建的用户权限, 并同时收回创建表空间权限(否则 imp 还是会自动为这些用户创建自动表空间)    grant connect,resource,dba to test;    revoke unlimited tablespace from username;

exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"

经常有人会问：原来的数据在USERS表空间里面，我想把它IMP进APP表空间，我已经修改了目的用户的默认表空间，为什么结果还是IMP到USERS表空间中了呢。

关于此问题，作如下解释：   Oracle并没有提供什么参数来指定要导入哪个表空间，数据默认将导入到原本导出时数据所在的表空间中，但是我们可以通过以下的方法来实现导入到不同的表空间。

1．在IMP时候使用INDEXFILE参数   当给此参数指定了某一文件名，IMP的时候所有的index将不会直接导入到表空间中，而是在指定的文件中生成创建index的脚本。   然后用文本编辑器打开此文件，直接编辑脚本中的storage参数，修改为想要导入的表空间名称。

然后重新执行IMP，使用INDEXS=n参数将除Index之外的Objects导入。   最后进入SQL*PLUS，直接运行刚才编辑的脚本，生成索引。

该方法适用于将index以及constraints导入指定的表空间。

2．改变目的用户的默认表空间

这就是上面说的经常有人提问的方法。但是上述的问题之所以没有成功，是因为缺少了下面的几步。

首先，收回目的用户的”UNLIMITED TABLESPACE”权限：

revoke unlimited tablespace from username; 其次，取消目的用户在原数据导出表空间中的配额，这样才能迫使IMP把数据导入到用户的默认表空间中去。 然后，将希望导入的表空间设为目的用户的默认表空间，并添加配额。 最后，执行IMP。

、带有查询语句的导出  exp file=filename.dmp triggers=n full=n tables=table_name query="where rownum <10000"  (其中query后面的非数字字母字符均需要转义字符，引号用"")

、标准的导出语句 exp userid/password tables=table_name file=filename.dmp

二. 用户模式 这种模式虽然不会创建表空间, 而在创建table的时候, 会指定物理的表空间位置, 换句话说, 如果在目标数据库中没有以前这个表的表空间存在, 那么就会放到默认表空间, 否则会放到与之前一样的表空间. 表模式, 个人感觉应该跟这个差不多.

最后, 个人觉得, 跨平台, 最好还是使用用户模式.

只有完全相同的数据库之间, 推荐使用 full 模式.

Leave a Reply