管道是linux上的一种结构,可用于两个shell之间的通信。
常规的导入导出需要先生成 导出文件 (dmp) 然后再由导入程序把dmp文件的内容导入到目标库。
但是,如果使用管道,可以避免导入文件的生成。理论上可以减少一半的时间。
先建立管道
[oracle@server ~]$ mknod imp_exp_pipe p
[oracle@server ~]$ ll
total 208824
-rw-r--r-- 1 root root 3540 Apr 9 22:53 AA.sql
-rw-r--r-- 1 root root 17125 Apr 9 22:43 AA.txt
-rw-r--r-- 1 oracle oinstall 213594112 Jul 20 10:42 Bonnie.4187.000
-rw-r--r-- 1 oracle oinstall 0 Jul 20 10:41 Bonnie.4187.001
-rw-r--r-- 1 oracle oinstall 0 Jul 20 10:41 Bonnie.4187.002
-rw-r--r-- 1 oracle oinstall 0 Jul 20 10:41 Bonnie.4187.003
drwxr-xr-x 8 oracle oinstall 4096 Sep 22 2011 database
prw-r--r-- 1 oracle oinstall 0 Aug 11 22:20 imp_exp_pipe
-rw-r--r-- 1 oracle oinstall 0 Apr 16 16:24 my_export.log
-rw-r--r-- 1 oracle oinstall 0 Apr 16 16:24 my_export.pipe
prw-r--r-- 1 oracle dba 0 Apr 16 16:19 pipe_export
使用管道导出
[oracle@server ~]$ exp \'sys/admin1@orcl as sysdba \' rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 tables=test file=imp_exp_pipe log=exp_fulldb_yyyymmdd.log;
Export: Release 11.2.0.3.0 - Production on Sun Aug 11 22:41:27 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: indexes on tables will not be exported
About to export specified tables via Conventional Path ...
. . exporting table TEST
75571 rows exported
Export terminated successfully without warnings.
导入
[oracle@server ~]$ imp \'sys/admin1@orcl as sysdba \' file=imp_exp_pipe log=imp.log full=y ;
Import: Release 11.2.0.3.0 - Production on Sun Aug 11 22:42:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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 US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "TEST" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SU"
"BOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "O"
"BJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP""
" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" V"
"ARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VAR"
"CHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6"
"5536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DE"
"FAULT) LOGGING NOCOMPRESS"
Import terminated successfully with warnings.
tns配置文件 如下
[oracle@server admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/OraHome/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.55)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.56)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)