Oracle exp与imp的使用详解

导入/导出是ORACLE幸存的最古老的两个命令行工具，其实我从来不认为Exp/Imp 是一种好的备份方式，正确的说法是Exp/Imp只能是一个好的转储工具，特别是在小型数据库的转储，表空间的迁移，表的抽取，检测逻辑和物理冲突等中有 不小的功劳。当然，我们也可以把它作为小型数据库的物理备份后的一个逻辑辅助备份，也是不错的建议。对于越来越大的数据库，特别是TB级数据库和越来越多 数据仓库的出现，EXP/IMP越来越力不从心了，这个时候，数据库的备份都转向了RMAN和第三方工具。下面说明一下EXP/IMP的使用。

一.【用 exp 数 据 导 出】

有三种主要的方式（完全、用户、表）

EXP的所有参数（括号中为参数的默认值）：

USERID用户名/口令如：USERID=duanl/duanl

FULL导出整个数据库 (N)

BUFFER数据缓冲区的大小

OWNER所有者用户名列表,你希望导出哪个用户的对象，就用owner=username

FILE输出文件(EXPDAT.DMP)

TABLES表名列表 ,指定导出的table名称，如：TABLES=table1,table2

COMPRESS导入一个extent(Y)

RECORDLENGTHIO记录的长度

GRANTS导出权限 (Y)

INCTYPE增量导出类型

INDEXES导出索引 (Y)

RECORD跟踪增量导出 (Y)

ROWS导出数据行 (Y)

PARFILE参数文件名,如果你exp的参数很多，可以存成参数文件.

CONSTRAINTS导出约束 (Y)

CONSISTENT交叉表一致性

LOG屏幕输出的日志文件

STATISTICS分析对象(ESTIMATE)

DIRECT直接路径 (N)

TRIGGERS导出触发器 (Y)

FEEDBACK显示每 x 行 (0) 的进度

FILESIZE各转储文件的最大尺寸

QUERY选定导出表子集的子句

下列关键字仅用于可传输的表空间

TRANSPORT_TABLESPACE导出可传输的表空间元数据 (N)

TABLESPACES将传输的表空间列表

Exp导出举例如下:

1、完全：

EXP SYSTEM/MANAGERBUFFER=64000 FILE=C:\FULL.DMP FULL=Y

如果要执行完全导出，必须具有特殊的权限

2、用户模式：

EXP SONIC/SONICBUFFER=64000FILE=C:\SONIC.DMP OWNER=SONIC

这样用户SONIC的所有对象被输出到文件中。

3、表模式：

EXP SONIC/SONICBUFFER=64000FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)

这样用户SONIC的表SONIC就被导出

数据库逻辑备份方法

ORACLE数据库的逻辑备份分为三种模式：表备份、用户备份和完全备份。

表模式

备份某个用户模式下指定的对象（表）。业务数据库通常采用这种备份方式。

若备份到本地文件，使用如下命令：

exp icdmain/icd rows=y indexes=n compress=nbuffer=65536

feedback=100000 volsize=0

file=exp_icdmain_csd_yyyymmdd.dmp

log=exp_icdmain_csd_yyyymmdd.log

tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo

若直接备份到磁带设备，使用如下命令：

exp icdmain/icd rows=y indexes=n compress=nbuffer=65536

feedback=100000 volsize=0

file=/dev/rmt0

log=exp_icdmain_csd_yyyymmdd.log

tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo

注：在磁盘空间允许的情况下，应先备份到本地服务器，然后再拷贝到磁带。出于速度方面的考虑，尽量不要直接备份到磁带设备。

用户模式

备份某个用户模式下的所有对象。业务数据库通常采用这种备份方式。

若备份到本地文件，使用如下命令：

exp icdmain/icd owner=icdmain rows=y indexes=ncompress=n buffer=65536

feedback=100000 volsize=0

file=exp_icdmain_yyyymmdd.dmp

log=exp_icdmain_yyyymmdd.log

若直接备份到磁带设备，使用如下命令：

exp icdmain/icd owner=icdmain rows=y indexes=ncompress=n buffer=65536

feedback=100000 volsize=0

file=/dev/rmt0

log=exp_icdmain_yyyymmdd.log

注：如果磁盘有空间，建议备份到磁盘，然后再拷贝到磁带。如果数据库数据量较小，可采用这种办法备份。

以下为详细的导入导出实例：

1、 将数据库TEST完全导出，用户名system 密码manager 导出到D：\daochu.dmp中

expsystem/manager@TEST file=d：\daochu.dmp full=y

2、 将数据库中system用户与sys用户的表导出

expsystem/manager@TEST file=d：\daochu.dmp owner=（system，sys）

3、 将数据库中的表table1 、table2导出

expsystem/manager@TEST file=d：\daochu.dmp tables=（table1，table2）

4、 将数据库中的表table1中的字段filed1以"00"打头的数据导出

expsystem/manager@TEST file=d：\daochu.dmp tables=（table1） query=\" where filed1 like'00%'\"

上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。

不过在上面命令后面 加上 compress=y就可以了

EXP常用选项

1.FULL，这个用于导出整个数据库，在ROWS=N一起使用时，可以导出整个数据库的结构。例如：

exp userid=test/test file=./db_str.dmplog=./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.dmplog=./db_str.log owner=duanl

exp userid=test/test file=./db_str.dmplog=./db_str.log table=nc_data,fi_arap

3.BUFFER和FEEDBACK，在导出比较多的数据时，我会考虑设置这两个参数。例如：

exp userid=test/test file=yw97_2003.dmplog=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。

6. FILESIZE该选项在8i中可用。如果导出的dmp文件过大时，最好使用FILESIZE参数，限制文件大小不要超过2G。如：

exp userid=duanl/duanl file=f1,f2,f3,f4,f5filesize=2G owner=scott

这样将创建f1.dmp, f2.dmp等一系列文件，每个大小都为2G，如果导出的总量小于10G

EXP不必创建f5.bmp.

二.【用 imp 数 据 导 入】

IMP的所有参数（括号中为参数的默认值）：

USERID用户名/口令

FULL导入整个文件 (N)

BUFFER数据缓冲区大小

FROMUSER所有人用户名列表

FILE输入文件(EXPDAT.DMP)

TOUSER用户名列表

SHOW只列出文件内容 (N)

TABLES表名列表

IGNORE忽略创建错误 (N)

RECORDLENGTHIO记录的长度

GRANTS导入权限 (Y)

INCTYPE增量导入类型

INDEXES导入索引 (Y)

COMMIT提交数组插入 (N)

ROWS导入数据行 (Y)

PARFILE参数文件名

LOG屏幕输出的日志文件

CONSTRAINTS导入限制 (Y)

DESTROY覆盖表空间数据文件(N)

INDEXFILE将表/索引信息写入指定的文件

SKIP_UNUSABLE_INDEXES跳过不可用索引的维护(N)

ANALYZE执行转储文件中的ANALYZE 语句 (Y)

FEEDBACK显示每 x 行 (0) 的进度

TOID_NOVALIDATE跳过指定类型 id 的校验

FILESIZE各转储文件的最大尺寸

RECALCULATE_STATISTICS重新计算统计值 (N)

下列关键字仅用于可传输的表空间

TRANSPORT_TABLESPACE导入可传输的表空间元数据 (N)

TABLESPACES将要传输到数据库的表空间

DATAFILES将要传输到数据库的数据文件

TTS_OWNERS拥有可传输表空间集中数据的用户

1.它是操作系统下一个可执行的文件 存放目录/ORACLE_HOME/bin

imp导入工具将EXP形成的二进制系统文件导入到数据库中.

它有三种模式：

a.用户模式：导出用户所有对象以及对象中的数据；

b.表模式：导出用户所有表或者指定的表；

c.整个数据库：导出数据库中所有对象。

只有拥有IMP_FULL_DATABASE和DBA权限的用户才能做整个数据库导入

imp步骤：

(1) create table(2) insert data(3)create index (4)create triggers,constraints

2.导入工具imp交互式命令行方式的例子

$ imp

Import: Release 8.1.6.0.0 - Production on星期五 12月 717:01:08 2001

(c) Copyright 1999 Oracle Corporation.All rights reserved.

用户名:test

口令:****

连接到: Oracle8i Enterprise Edition Release 8.1.6.0.0-64bit Production

With the Partitioning option

JServer Release 8.1.6.0.0 - Production

导入文件: expdat.dmp> /tmp/m.dmp

输入插入缓冲区大小（最小为 8192 ) 30720>

经由常规路径导出由EXPORT:V08.01.06创建的文件

警告: 此对象由 TEST 导出, 而不是当前用户

已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入

只列出导入文件的内容(yes/no)：no>

由于对象已存在, 忽略创建错误(yes/no)：no> yes

导入权限(yes/no)：yes>

导入表数据(yes/no)：yes>

导入整个导出文件(yes/no)：no> yes

. 正在将TEST的对象导入到 SCOTT

. . 正在导入表"CMAMENU"4336行被导入

成功终止导入，但出现警告。

3.导入工具imp非交互式命令行方式的例子

$ imp system/manager fromuser=jones tables=(accts)

$ imp system/manager fromuser=scott tables=(emp,dept)

$ imp system/manager fromuser=scott touser=joe tables=emp

$ imp scott/tiger file = expdat.dmp full=y

$ imp scott/tiger file = /mnt1/t1.dmp show=n buffer=2048000 ignore=ncommit=ygrants=y full=y log=/oracle_backup/log/imp_scott.log

$ imp system/manager parfile=params.dat

params.dat内容

file=dba.dmp show=n ignore=n grants=y fromuser=scott tables=(dept,emp)

4.导入工具imp可能出现的问题

(1)数据库对象已经存在

一般情况, 导入数据前应该彻底删除目标数据下的表, 序列, 函数/过程,触发器等;

数据库对象已经存在, 按缺省的imp参数, 则会导入失败

如果用了参数ignore=y, 会把exp文件内的数据内容导入

此时，

如果（目标）表【即被导入的表】有唯一关键字的约束条件, 不合条件将不被导入

如果（目标）表【即被导入的表】没有唯一关键字的约束条件, 将引起记录重复

(2)数据库对象有主外键约束

不符合主外键约束时, 数据会导入失败

解决办法: 先导入主表, 再导入依存表

(或)disable目标导入对象的主外键约束, 导入数据后, 再enable它们

(3)权限不够

如果要把A用户的数据导入B用户下, A用户需要有imp_full_database权限

(4)导入大表( 大于80M ) 时, 存储分配失败

默认的EXP时, compress = Y, 也就是把所有的数据压缩在一个数据块上.

导入时, 如果不存在连续一个大数据块, 则会导入失败.

导出80M以上的大表时, (exp)记得compress=N, 则不会引起这种错误.

(5) imp和exp使用的字符集不同

如果字符集不同,导入会失败, 可以改变unix环境变量或者NT注册表里NLS_LANG相关信息.

导入完成后再改回来.

(6) imp和exp版本不能往上兼容

imp可以成功导入低版本exp生成的文件, 不能导入高版本exp生成的文件

根据情况我们可以用

$ imp username/password@connect_string

说明: connect_string 是在/ORACLE_HOME/network/admin/tnsnames.ora

定义的本地或者远端数据库的名称

注意事项:

UNIX:/etc/hosts要定义本地或者远端数据库服务器的主机名

win98:windows\hosts和IP地址的对应关系

EXP可以导出成多个DUMP文件

很多DBA还没有意识到Export可以使用多个数据文件来存储被导出的数据。如果单个磁盘卷没有足够的空间，或者数据库超出了操作系统中单个文件的大小限制，它很容易将导出数据分割成多个文件。

早在Oracle 8i开始，FILE参数就可以容纳用逗号分开的多个文件路径。FILESIZE参数让你指定在转移到下一个文件之前，多少数据可以写到单个文件中。如果Export用光了FILE列表中的名字，它会提示额外的文件名。

比如，让我们假设全部的数据库导出需要6G的空间，并且操作系统中单个文件大小的限制是2G。你要将导出文件放在/exp文件系统下的prod目录中。这个参数文件应该包含下面的内容：

在Oracle 10g中，新的Data Pump export(expdp)使用方式类似但是略有改变。输出文件路径不再像早期版本中那样写死，相反，使用Directory对象指到操作系统中的目录。 FILE参数被DUMPFILE参数取代，并且为了方便使用，你可以指定通配符来自动产生文件名而不是全部列出来。

在Oracle 10g中，如果你要创建一个叫做EXPDIR的Directory指向/exp/prod目录，上面的例子中的参数会变成这样：

在Export过程中文件名exp01.dmp、exp02.dmp和exp03.dmp会自动产生。

IMP常用选项

1、FROMUSER和TOUSER，使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。例如：假设我们做exp时导出的为test的对象,现在我们想把对象导入用户：

imp userid=test1/test1 file=expdat.dmpfromuser=test1 touser=test1

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

导入一张或几张表

导入用户下的表

导入数据库

impscott/tiger@itcastclient file=D:/c.dmplog=D:/log.logfully=y ignore=y destroy=y;

exp和imp的提示模式

表空间传输

表空间传输是8i新增加的一种快速在数据库间移动数据的一种办法，是把一个数据库上的格式数据文件附加到另外一个数据库中，而不是把数据导出成Dmp文件，这在有些时候是非常管用的，因为传输表空间移动数据就象复制文件一样快。

关于传输表空间有一些规则，即：

·源数据库和目标数据库必须运行在相同的硬件平台上。

·源数据库与目标数据库必须使用相同的字符集。

·源数据库与目标数据库一定要有相同大小的数据块

·目标数据库不能有与迁移表空间同名的表空间

·SYS的对象不能迁移

·必须传输自包含的对象集

·有一些对象，如物化视图，基于函数的索引等不能被传输

可以用以下的方法来检测一个表空间或一套表空间是否符合传输标准：

execsys.dbms_tts.transport_set_check(‘tablespace_name’,true);

select * from sys.transport_set_violation;

如果没有行选择，表示该表空间只包含表数据，并且是自包含的。对于有些非自包含的表空间，如数据表空间和索引表空间，可以一起传输。

以下为简要使用步骤，如果想参考详细使用方法，也可以参考ORACLE联机帮助。

1.设置表空间为只读（假定表空间名字为APP_Data 和APP_Index）

alter tablespace app_data read only;

alter tablespace app_index read only;

2.发出EXP命令

SQL>host exp userid=”””sys/password assysdba”””

transport_tablespace=y tablespace=(app_data,app_index)

以上需要注意的是

·为了在SQL中执行EXP，USERID必须用三个引号，在UNIX中也必须注意避免“/”的使用

·在816和以后，必须使用sysdba才能操作

·这个命令在SQL中必须放置在一行（这里是因为显示问题放在了两行）

3.拷贝数据文件到另一个地点，即目标数据库

可以是cp(unix)或copy(windows)或通过ftp传输文件（一定要在bin方式）

4.把本地的表空间设置为读写

5.在目标数据库附加该数据文件

imp file=expdat.dmp userid=”””sys/password assysdba””” transport_tablespace=y “datafile=(c:\temp\app_data,c:\temp\app_index)”

6.设置目标数据库表空间为读写

alter tablespace app_data read write;

alter tablespace app_index read write;

优化EXP/IMP的方法：

当需要exp/imp的数据量比较大时，这个过程需要的时间是比较长的，我们可以用一些方法来优化exp/imp的操作。

exp:使用直接路径 direct=y

oracle会避开sql语句处理引擎,直接从数据库文件中读取数据,然后写入导出文件.

可以在导出日志中观察到: exp-00067: table xxx willbe 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;

PARAMETERVALUE

-----------------------------------------------------

NLS_LANGUAGEAMERICAN

NLS_TERRITORYAMERICA

NLS_CURRENCY$

NLS_ISO_CURRENCYAMERICA

NLS_NUMERIC_CHARACTERS.,

NLS_CHARACTERSETZHS16GBK

NLS_CALENDARGREGORIAN

NLS_DATE_FORMATDD-MON-RR

NLS_DATE_LANGUAGEAMERICAN

NLS_SORTBINARY

NLS_TIME_FORMATHH.MI.SSXFF AM

NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZH:TZM

NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZH:TZM

NLS_DUAL_CURRENCY$

NLS_COMPBINARY

NLS_NCHAR_CHARACTERSETZHS16GBK

NLS_RDBMS_VERSION8.1.7.4.1

NLS_CHARACTERSET：ZHS16GBK是当前数据库的字符集。

我们再来查看客户端的字符集信息：

客户端字符集的参数NLS_LANG=_.

language：指定oracle消息使用的语言，日期中日和月的显示。

Territory：指定货币和数字的格式，地区和计算星期及日期的习惯。

Characterset：控制客户端应用程序使用的字符集。通常设置或等于客户端的代码页。或者对于unicode应用设为UTF8。

在windows中，查询和修改NLS_LANG可在注册表中进行：

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOMExx\

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 whererole='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工具。