ITPub博客

首页 > 数据库 > Oracle > [20190520]exp imp on th fly.txt

[20190520]exp imp on th fly.txt

原创 Oracle 作者:lfree 时间:2019-05-20 09:07:24 0 删除 编辑

[20190520]exp imp on th fly.txt


--//以前做的测试,查找浪费许多时间,做1个记录。

--//注:仅仅linux 操作系统,bash shell版本不能太低就可以实现,现在安装的linux bash shell应该都没有问题。


1.环境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


2.测试exp:

exp on the fly, run:

create table t as select * from all_objects;


$ exp scott/book file=>(gzip>t.dmp.gz)  tables=t buffer=8388608

Export: Release 11.2.0.4.0 - Production on Mon May 20 08:58:15 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                              T      83245 rows exported

Export terminated successfully without warnings.


$ file t.dmp.gz

t.dmp.gz: gzip compressed data, from Unix, last modified: Mon May 20 08:58:15 2019


SCOTT@book> rename t to ty;

Table renamed.


3.测试imp:

imp on the fly, run:


$ imp scott/book full=y  file=<(gunzip <t.dmp.gz) buffer=8388608

Import: Release 11.2.0.4.0 - Production on Mon May 20 09:00:42 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. importing SCOTT's objects into SCOTT

. . importing table                            "T"      83245 rows imported

Import terminated successfully without warnings.

imp scott/book full=y  file=<(gunzip <t.dmp.gz) buffer=8388608


SCOTT@book> select count(*) from t union all select  count(*) from ty;

  COUNT(*)

----------

     83245

     83245


4.也可以通过管道实现所谓的不落地方式,只要建立将p.dmp.gz建立为管道文件:

$ mkfifo p.dmp.gz

$ ls -l p.dmp.gz

prw-r--r-- 1 oracle oinstall 0 2019-05-20 09:04:27 p.dmp.gz


--//再执行如下:

$ cat ad.sh

#! /bin.bash

exp scott/book file=>(gzip>p.dmp.gz)  tables=t buffer=8388608 &

sleep 5

imp scott/btbtms@192.168.100.33:1521/test full=y  file=<(gunzip <p.dmp.gz) buffer=8388608 &

wait


SCOTT@test> select count(*) from t ;

  COUNT(*)

----------

     83245


--//注expdp,impdp不支持这样方式,实际上expdp,impdp已经支持数据压缩(早期仅仅支持元数据),没有必要这样做,意义不大。


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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2435
  • 访问量
    6216316