ITPub博客

首页 > 数据库 > Oracle > exp和imp详解

exp和imp详解

Oracle 作者:记录每一次错误 时间:2020-10-24 22:26:47 0 删除 编辑

 之前写了关于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=qj  statistics=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.log
tables=(t1,t2)  
query="where object_id<1"
statistics=none
结果如图:

<4>grants,indexes,constraints,trigger
exp qj/**@local file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log  tables=(t1,t2 )   statistics=none  grants=n
解释:grants指定是否导出对象的授权信息,授权信息指的是通过grant命令授予的对象访问,操作权限,默认为Y,也就是默认导出;indexes指定是否导出表的索引,默认为Y;constraints指定是否导出表的约束,默认为Y;triggers是否导出与表相关的触发器,默认为Y。
<5>filesize
exp qj/**@local  filesize=10m  file=C:\Users\QuJian\Desktop\qj1.dmp,C:\Users\QuJian\Desktop\qj2.dmp 
 owner=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.dmp log=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.sql  ignore=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的路上是不孤独的,因为你有着众多的同行者,欢迎大家加入下面的数据库讨论群,让我们一起互助前行。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31547066/viewspace-2729494/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
性格开朗,有较强的学习能力,对oracle数据库的体系结构,搭建RAC,timesten,goldengate,分布式数据库,dataguard,系统调优有较深入的了解, 尤其是oracle优化,深入学习的主机命令,对数据库的优化,SQL语句的优化有深入的认识,目前正在shell脚本,mysql,以后会有计划学习大数据和python。

注册时间:2018-07-23

  • 博文量
    183
  • 访问量
    338574