[20190520]exp imp on th fly.txt

[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已經支援資料壓縮(早期僅僅支援後設資料),沒有必要這樣做，意義不大。