oracle导出时负载,exp和imp详解

之前写了关于expdp和impdp的文章，感兴趣的可以移步http://blog.itpub.net/30485601/viewspace-2151455/，由于expdp和impdp是oracle10g之后才有的，如果数据库版本在10g之前，那么就不能使用expdp和impdp了。但是我们却可以使用exp和imp来实现逻辑导入导出功能，其实expdp和impdp本身就是在exp和imp的基础上发展起来的，与之不同的是，exp和imp是一个客户端工具，可以在客户端进行操作，而且只需要resource权限就可以了，所以对于日常的数据迁移还是很方便的。

如果先看了expdp和impdp的话，本篇文章看起来就会觉得简陋很多，因为两个本身从实用性来说的话，当然长江后浪推前浪了，其实这要看和谁对比了，现在的大牛比两年前的大牛厉害，但人家2年前依旧是一个大牛。那到底是用expdp呢，还是exp呢，其实和使用场景有关，如果你是一个系统管理员，拥有DBA权限，那么在数据迁移的时候，当然我觉得可以给自己分配一个拥有访问系统目录和expdp权限的用户，可以尽情体验expdp带来的快感；如果你只是一个普通用户，是由别人分配给你的权限，比如：你的数据库服务器是托管的，托管方一个库上负载了多个库，你的库只是人家管理的其中一个，那么对方一般情况下是不会给你一个高权限的用户的，你所拥有的只是connect和resource权限，在这种情况下，你可以使用exp，关键是你也没有权限用expdp，对于日常的迁移备份，exp和imp绝对是绰绰有余了。看了好一会儿文字，估计看客们心情都放松了吧，那老司机我就要发车了。

准备工作：如果需要在客户端进行exp和imp的话，需要在客户端下载完整版的客户端，然后配置环境变量tnsnames.ora就可以了。

一、exp

1、导出schemas

exp qj/**@local file=C:\Users\QuJian\Desktop\qj.dmp log=C:\Users\QuJian\Desktop\qj.log owner=qjstatistics=none

解释：exp和imp都是操作系统命令，在操作系统级别操作。exp表示这是一个exp导出操作；qj/**，其中qj是用户名，**是用户密码，对于qj这个用户，如果只有resource权限，那么只能导出qj自己，如果具有exp_full_database,那么将会拥有导出整个库的权限，当然也可以导出其他schemas了；@local，local是tnsmames(网络服务名)，需要在客户端配置环境变量TNS_NAME，可以通过路径找到这个tnsnames，tnsnames记录了数据库服务器的地址和服务名,通过local就可以找到数据库服务器的地址；file表示数据导出的位置，导出文件以.dmp后缀，log表示导出日志,导出日志以.log后缀;owner指明要导出的schema，如果具有exp_full_database权限的话，那么还可以导出其他schema，多个schema用逗号(,)隔开，比如：owner=(qj,scott);由于客户端字符集和服务器字符集不一致，因此会出现EXP-00091 正在导出有问题的统计信息这个错误，加上statistics=none可以解决这个错误。下面贴出正常导出的过程和没有加statistics=none导出过程：

如图：

正常导出的过程

对于exp-00104错误，目前只能通过expdp来解决

没有加statistics=none导出如图：

可以看到，出现了大量的exp-00091错误。

2、导出某些表,我将以导出表为例，列出一些常用的参数

<1>tables

exp qj/**@local file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2)statistics=none

解释：tables参数指明导出哪些表，多个表之间用逗号(,)分隔

结果如图：

<2>query

exp qj/**@local file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2)query=\"where object_id<1\"statistics=none

解释：query参数可以导出指定条件的记录，但是这里有一点需要注意，expdp的query参数如果只指定了一个表的条件，其他表没有指定条件，意味着其他表全导。但是对于exp，这个query满足的字段必须是所有表都有的，如果在这里直接写t1.object_id<1就会报错，所以，where指出的条件，必须是要导出的表都有的，要不然只能分类导出表了。

结果如图：

<3>parfile

exp qj/**@local parfile=C:\Users\QuJian\Desktop\par.txt解释：parfile可以直接调用命令文件，可以看到<2>中，加query时还需要转义字符，这个简直是太麻烦了，而且命令过长，超出了界面所能容纳的上限还会报错，主要是还不美观。如果加了parfile参数，那么我们只需要在命令行写出exp qj/**@local parfile直接调用写好的命令就可以了。

pafile文本如下：

file=C:\Users\QuJian\Desktop\par.dmp

log=C:\Users\QuJian\Desktop\par.logtables=(t1,t2)query="where object_id<1"statistics=none

结果如图：

<4>grants,indexes,constraints,triggerexp qj/**@local file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.logtables=(t1,t2)statistics=none grants=n

解释：grants指定是否导出对象的授权信息，授权信息指的是通过grant命令授予的对象访问，操作权限，默认为Y，也就是默认导出；indexes指定是否导出表的索引，默认为Y；constraints指定是否导出表的约束，默认为Y;triggers是否导出与表相关的触发器，默认为Y。

<5>filesize

exp qj/**@local filesize=10mfile=C:\Users\QuJian\Desktop\qj1.dmp,C:\Users\QuJian\Desktop\qj2.dmpowner=qj statistics=none

解释：filesize可以分割导出的.dmp文件，这里我将每一个.dmp文件设置为10m，超出10m就自动导入到下一个文件，为了使导出文件够大，在这里我导出了qj这个schema

结果如图：

<6>buffer

exp qj/**@local  file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2) statistics=none buffer=6400000

解释：buffer可以用来设置数据行缓冲区大小，适当设置可以提高导出的速度。buffer的大小是这么计算的，比如t1这个表有5个字段，2个number(2)，3个varchar2(20),一个number(2)占2个字节，一个varchar2(20)占20个字节，那么一行占2*2+20*3=64字节，我一共导出了10W行，那么buffer=64*100000=6400000，6400000/1024/1024=6M

<7>direct和recordlength

exp qj/**@local  file=C:\Users\QuJian\Desktop\t.dmplog=C:\Users\QuJian\Desktop\t.log tables=(t1,t2) statistics=none direct=y recordlength=6400000解释：direct采用了直接路径的方式，一般和recordlength参数配合使用，常规路径导出使用SQL SELECT语句从表中抽取数据，直接路径导出则是将数据直接从磁盘读到PGA再原样写入导出文件，从而避免了SQL命令处理层的数据转换过程，大大提高了导出效率，在数据量大的情况下，直接路径导出的效率优势更为明显，recordlength指定了I/O缓冲区的大小，和buffer类似。3、导出表空间

exp qj/***@local file=C:\Users\QuJian\Desktop\ceshi.dmp log=C:\Users\QuJian\Desktop\ceshi.log tablespaces=users statistics=none

解释：tablespaces指定要导出的表空间，多个表空间中间用逗号(,)隔开，导出表空间需要有exp_full_database权限。

结果如图：

4、导出全库

exp sys/**@local file=C:\Users\QuJian\Desktop\full.dmp log=C:\Users\QuJian\Desktop\full.log full=y statistics=none解释：full=y指明导出全库，导出全库的用户必须拥有exp_full_database的权限，在这我使用sys用户导出，用sys特权用户导出的，必须由sys特权用户导入。结果如图：

5、rows=n,表示只导出表结构

exp qj/**@local file=C:\Users\QuJian\Desktop\t1.dmp log=C:\Users\QuJian\Desktop\t1.log  statistics=none rows=n

更多参数，请参照exp help=y

二、imp

1、全部导入

不论导出的是schema,table,还是tablespace或者整个库，如果全部导入的话，都可以同等处理，当然，导入tablespace和全库需要有imp_full_database权限。

imp qj/***@local file=C:\Users\QuJian\Desktop\ceshi.dmp log=C:\Users\QuJian\Desktop\ceshi.log fromuser=ceshi touser=qj

解释：imp表明这是一个导入操作，file要导入的源文件路径，log导入日志生成的路径，fromuser从哪个用户导出的数据，touser导入到哪个用户，如果不知道原始文件是从哪个用户导出来的，可以直接fromuser=(这里不写来源用户),导入操作会报错，这个报错就会提示源数据是从哪个用户导出来的，然后再次执行导入操作就可以了。

结果如图：

2、ignore参数

如果即将导入的库中已经有了此表，或者此表的表结构发生了变化(比如由char变成了varchar2)，那么导入就会报如图错误：

这个时候，只要加上ignore=y就可以了，

语句如下：imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi ignore=y

结果如图：

3、tables参数，full参数，parfile参数imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi tables=(demo)

imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi full=y

imp qj/***@local  parfile=C:\Users\QuJian\Desktop\par.txt

更多imp参数，可以通过imp help=y查询；

4、分区表的导入，index参数

如果导出数据中有分区表，那么在目标库没有相同DDL建表语句的时候就会报错，解决报错有两种方法：

(1)在导入数据库中，与被导入表的分区相关的表空间已对等建立好(表空间名也是与exp数据库中一致的)，那么这种情况，与普通表的imp操作是一样的;

(2)但多数情况下，我们不知道exp数据库中该表的物理结构，此时，可以执行以下步骤:

<1>从源目标表中获取分区表的DDL

语句如下：

imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi tables=(t1) index=C:\Users\QuJian\Desktop\t1.sqlignore=y这样一来，就将源表的DDL导出到C:\Users\QuJian\Desktop\t1.sql，接下来执行第二步

<2>编辑这个分区表的DDL，如果希望和源库的DDL一致，那么就在目标库建立相同名称的表空间，之后执行这个DDL语句在目标库先建立分区表，然后执行导入操作(加ignore=y)；如果希望用目标库的表空间名，那么将从源库导出的分区表DDL中的表空间名编辑成目标库的表空间，执行DDL在目标库先建立分区表，然后执行导入操作(加ignore=y)。

三、

exp和imp参数众多，不过我们也完全不用记那么多，只要知道怎么用就可以了，具体的可以exp/imp help=y，你就会知道怎么用的，备份作为处理数据问题最重要的手段，exp给我们提供了巨大的帮助，还是值得好好掌握的。学习oracle的路上是不孤独的，因为你有着众多的同行者，欢迎大家加入下面的数据库讨论群，让我们一起互助前行。