Oracle exp/imp导出导入工具的使用


说明:在exp里面加上导出emp的查询条件job='salesman' and sal<1600
???
??? (但我个人很少这样用,还是把满足条件的记录生成临时表后,再exp会方便一些)

$exp parfile=username.par file=/directory1/username_1.dmp,/directory1/username_2.dmp filesize=2000M log=/directory2/username_exp.log

参数文件username.par内容
userid=username/userpassword
buffer=8192000
compress=n
grants=y

说明:username.par为导出工具exp用的参数文件,里面具体参数可以根据需要去修改

???? filesize指定生成的二进制备份文件的最大字节数
????
???? (可用来解决某些OS下2G物理文件的限制及加快压缩速度和方便刻历史数据光盘等)

二.导入工具 imp

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月 7 17: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=n commit=y grants=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以上的大表时, 记得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地址的对应关系?

win2000:? winnt\system32\drivers\etc\hosts

?

ORACLE?EXP/IMP的使用详解　　导入/导出是ORACLE幸存的最古老的两个命令行工具，其实我从来不认为Exp/Imp是一种好的备份方式，正确的说法是Exp/Imp只能是一个好的转储工具，特别是在小型数据库的转储，表空间的迁移，表的抽取，检测逻辑和物理冲突等中有不小的功劳。当然，我们也可以把它作为小型数据库的物理备份后的一个逻辑辅助备份，也是不错的建议。对于越来越大的数据库，特别是TB级数据库和越来越多数据仓库的出现，EXP/IMP越来越力不从心了，这个时候，数据库的备份都转向了RMAN和第三方工具。下面说明一下EXP/IMP的使用。
　　如何使exp的帮助以不同的字符集显示：set?nls_lang=simplified?chinese_china.zhs16gbk，通过设置环境变量，可以让exp的帮助以中文显示，如果set?nls_lang=American_america.字符集，那么帮助就是英文的了
程序代码EXP的所有参数（括号中为参数的默认值）：
USERID???????用户名/口令?????如：?USERID=duanl/duanl??????
FULL?????????导出整个数据库?(N)
BUFFER???????数据缓冲区的大小?????????
OWNER???????所有者用户名列表,你希望导出哪个用户的对象，就用owner=username
FILE??????????输出文件?(EXPDAT.DMP)????
TABLES???????表名列表?,指定导出的table名称，如：TABLES=table1,table2
COMPRESS????导入一个extent?(Y)???
RECORDLENGTH??IO?记录的长度
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?将传输的表空间列表

程序代码IMP的所有参数（括号中为参数的默认值）：
USERID???用户名/口令???????????
FULL?????导入整个文件?(N)
BUFFER???数据缓冲区大小?????????
FROMUSER?????所有人用户名列表
FILE?????输入文件?(EXPDAT.DMP)????
TOUSER???????用户名列表
SHOW?????只列出文件内容?(N)
TABLES??????表名列表
IGNORE???忽略创建错误?(N)????
RECORDLENGTH??IO?记录的长度
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?拥有可传输表空间集中数据的用户

关于增量参数的说明：exp/imp的增量并不是真正意义上的增量，所以最好不要使用。
?

使用方法：
Exp?parameter_name=value?or?Exp?parameter_name=(value1,value2……)
只要输入参数help=y就可以看到所有帮助.

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。
6.?FILESIZE该选项在8i中可用。如果导出的dmp文件过大时，最好使用FILESIZE参数，限制文件大小不要超过2G。如：
exp?userid=duanl/duanl?file=f1,f2,f3,f4,f5?filesize=2G?owner=scott
这样将创建f1.dmp,?f2.dmp等一系列文件，每个大小都为2G，如果导出的总量小于10G
EXP不必创建f5.bmp.

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

表空间传输
????表空间传输是8i新增加的一种快速在数据库间移动数据的一种办法，是把一个数据库上的格式数据文件附加到另外一个数据库中，而不是把数据导出成Dmp文件，这在有些时候是非常管用的，因为传输表空间移动数据就象复制文件一样快。
关于传输表空间有一些规则，即：
·源数据库和目标数据库必须运行在相同的硬件平台上。
·源数据库与目标数据库必须使用相同的字符集。
·源数据库与目标数据库一定要有相同大小的数据块
·目标数据库不能有与迁移表空间同名的表空间
·SYS的对象不能迁移
·必须传输自包含的对象集
·有一些对象，如物化视图，基于函数的索引等不能被传输
可以用以下的方法来检测一个表空间或一套表空间是否符合传输标准：
exec?sys.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?as?sysdba”””?
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?as?sysdba”””?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?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=_<?territory?>.
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?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工具。