浅谈exp/imp(上) (r5笔记第81天)

作为DBA,经常需要在不同数据库环境间做数据的导入导出,exp/imp就是这样的轻便快捷的客户端工具,可以很方便的在不同数据库之间转移数据对象,即使数据库位于不同的硬件或者软件平台上。exp会转储产生对应的二进制文件,里面包含数据的定义信息,数据内容等,也就是我们通常所说的dump文件。

exp/imp是比较经典的数据导出导入工具,不过自expdp/impdp推出以来,exp/imp还是受到了不少的冷落,在新的数据库版本中,支持力度都集中在了expdp/impdp上面。Expdp/impdp是基于服务端的数据导入导出工具,性能上和功能上要更胜一筹,不过不管怎样,exp/imp确实是一款比较轻巧的工具,对于小表的处理效果还是相当不错的。

使用exp/imp的场景

使用exp/imp主要有4种模式:数据库模式,表模式,用户模式,可传输表空间模式,在此基础上使用比较多的还有基于query选项的数据选择性抽取和数据结构导出。

数据库模式

数据库模式主要是作为全库备份使用的,可以导出除sys之外的数据库里的所有对象,在数据量较小的情况下是一个不错的选择。

exp n1/n1 file=db_backup.dmp full=y

表模式

表模式可以导出某个用户下指定的表,比如我们需要导出的表名为:test1,test2

exp n1/n1 file=table_mode.dmp tables=test1,test2

用户模式

用户模式可以导出指定用户下所有的对象,比如导出用户user1下所有的对象

exp n1/n1 file=user_mode.dmp owner=user1

传输表空间模式常作为数据迁移时的一种方法,在数据迁移篇中会有详细的描述 海量数据迁移之传输表空间(一) http://blog.itpub.net/23718752/viewspace-1703358/

同理,imp中也会存在同样的四种模式,使用方法都是类似的。

除了常用的4种模式之外,使用query选项选择性导出数据也是一种很使用的方法。比如存在一个表test,我们希望根据字段object_type=’TABLE’的条件来选择性导出数据,可以使用query选项完成。

使用Query选项做选择性数据导出

exp n1/n1 file=query_bak.dmp query=\" where  object_type= \'TABLE\' \"  tables=test

得到对象的ddl语句

还有一个功能点可能大家不太注意,就是能够很方便得到ddl相关的语句。

比如我们希望得到表test的建表语句,可以通过exp/imp这么做。

exp n1/n1 rows=n compress=n tables=test file=test.dmp log=exp_test.log   buffer=10240000
imp n1/n1 rows=n full=y  ignore=y show=y file=test.dmp log=imp_test.log  buffer=10240000

得到的建表语句就会是下面的样子。

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing N1's objects into N1
. importing N1's objects into N1
 "CREATE TABLE "TEST" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" VA"
 "RCHAR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMB"
 "ER NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "C"
 "REATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMEST"
 "AMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATE"
 "D" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER NOT NULL ENABLE"
 ", "EDITION_NAME" VARCHAR2(30))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2"
 "55 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GRO"
 "UPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
 "CREATE INDEX "IDX_TEST" ON "TEST" ("OBJECT_ID" DESC , "OBJECT_NAME" , "OBJE"
 "CT_TYPE" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1"
 "048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"
 "ESPACE "USERS" LOGGING"

看起来确实是不太直观,如果自己想直接使用还得手工格式化,这个时候我们可以借助awk来格式化一下。看起来好像挺抽象,但是功能还是很强大的。

awk '
  / \"BEGIN /   { N=1; }
  / \"CREATE /   { N=1; }
  / \"CREATE INDEX/   { N=1; }
  / \"CREATE UNIQUE INDEX/  { N=1; }
  / \"ALTER /   { N=1; }
  / \" ALTER /   { N=1; }
  / \"ANALYZE /   { N=1; }
  / \"GRANT /    { N=1; }
  / \"COMMENT /   { N=1; }
  / \"AUDIT /     { N=1; }
  N==1 { printf "\n/\n"; N++ }
  /\"$/ {
    if (N==0) next;
    s=index( $0, "\"" );
    ln0=length( $0 )
    if ( s!=0 ) {
      lcnt++
      if ( lcnt >= 30 ) {
        ln=substr( $0,s+1,length( substr($0,s+1))-1)
        t=index( ln, ")," )
        if ( t==0 ) { t=index( ln, ", " ) }
        if ( t==0 ) { t=index( ln, ") " ) }
        if ( t > 0 ) {
          printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2)
          lcnt=0
        }
        else {
          printf "%s", ln
          if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }
        }
      }
      else {
        printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
        if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }
      }
    }
  }
  END { printf "\n/\n"}
' imp_test.log |sed '1,2d; /^$/ d;
s/STORAGE *(INI/~    STORAGE (INI/g;
s/, "/,~    "/g;
s/ (\"/~   &/g;
s/PCT[FI]/~    &/g;
s/[( ]PARTITION /~&/g;
s/) TABLESPACE/)~    TABLESPACE/g;
s/   , / ,~/g;
s/ DATAFILE  /&~/' | tr "~" "\n"

看看格式化后的结果,如此显著的结果是不是有一种马上想试试的冲动。

  CREATE TABLE "TEST"
    ("OWNER" VARCHAR2(30) NOT NULL ENABLE,
    "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
    "SUBOBJECT_NAME" VARCHAR2(30),
    "OBJECT_ID" NUMBER NOT NULL ENABLE,
    "DATA_OBJECT_ID" NUMBER,
    "OBJECT_TYPE" VARCHAR2(19),
    "CREATED" DATE NOT NULL ENABLE,
    "LAST_DDL_TIME" DATE NOT NULL ENABLE,
    "TIMESTAMP" VARCHAR2(19),
    "STATUS" VARCHAR2(7),
    "TEMPORARY" VARCHAR2(1),
    "GENERATED" VARCHAR2(1),
    "SECONDARY" VARCHAR2(1),
    "NAMESPACE" NUMBER NOT NULL ENABLE,
    "EDITION_NAME" VARCHAR2(30)) 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "USERS" LOGGING NOCOMPRESS
/
CREATE INDEX "IDX_TEST" ON "TEST"
    ("OBJECT_ID" DESC ,
    "OBJECT_NAME" ,
    "OBJECT_TYPE" ) 
    PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "USERS" LOGGING
/

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-06-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

SQL基础-->数据库事务(TRANSACTION)

数据库事务是指作为单个逻辑工作单元执行的一系列操作,可以认为事务就是一组不可分割的SQL语句

8630
来自专栏北京马哥教育

五分钟 SQL Server 学习入门——基本篇

? 作者:My_heart_ 来源:http://blog.csdn.net/my_heart_/article/details/62425140 首先相信...

41640
来自专栏数据库新发现

Oracle9i新特性-索引监视及注意事项[修正版]

Last Updated: Saturday, 2004-12-04 10:28 Eygle

10030
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(下)

使用performance_schema中的语句当前事件记录表和语句事件历史记录表可以查询数据库中最近执行的一些SQL语句,以及语句相关的信息,这里我们以eve...

27430
来自专栏数据和云

【动手实践】Oracle 12.2新特性:多列列表分区和外部表分区

在Oracle 12.2版本中,增加了大量的分区新特性,这其中包括: 自动的列表分区创建 在线的普通表转换分区表 支持只读分区和读写分区混合 以下介绍的三个特...

34450
来自专栏智能合约

数据插入失败引发的主键auto_increment问题

38430
来自专栏北京马哥教育

数据库基础知识:数据库中的约束和三大范式

? 一.数据库中的范式: 范式, 英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪70年代提出关系数据库模型后...

33070
来自专栏蓝天

MYSQL不能从远程连接的一个解决方法

ERROR 1130: Host '192.168.1.3' is not allowed to connect to this MySQL server

10120
来自专栏程序你好

MySQL中MyISAM与InnoDB存储的区别

13410
来自专栏程序猿

SQL 注入语句特征

语句特征 1.判断有无注入点 ; and 1=1 and 1=2 2.猜表一般的表的名称无非是admin adminuser user pass passwor...

542110

扫码关注云+社区

领取腾讯云代金券