How to Export into Import via a Pipe Over SQL Net
Description
The following guide explains how to perform and import/export via a pipe over SQL net.
Step-By-Step
1. From a Unix userid create the export parfile.
Example
userid=/
tables=mytable1,mytable2
log=myexport.log
file=myexport.pip
compress=n
rows=y
2. From a Unix userid create the import parfile.
Example
userid=system/<password>@SID
fromuser=OPS$ORACLE
touser=myuser
tables=mytable1,mytable2
file=myexport.pip
log=myimport.log
3. From a Unix userid create a pipe file.
- mknod myexport.pip p
4. From a Unix userid start a nohup background import.
- nohup imp parfile=myimport.par &
5. From a Unix userid run the export.
- exp parfile=myexport.par
Example Output
oracle> sqlplus /
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Oct 6 14:02:57 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table msr1 as select * from dba_tables;
Table created.
SQL> create table msr2 as select * from dba_tables;
Table created.
SQL> select count(*) from msr1;
COUNT(*)
----------
1395
SQL> select count(*) from msr2;
COUNT(*)
----------
1396
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
oracle>
oracle> ssh myhost1
##############################################################################
* * * * W A R N I N G * * * *
YOU MUST NOT ACCESS THIS SYSTEM, OR ATTEMPT TO ACCESS THIS SYSTEM, UNLESS
YOU ARE AN AUTHORISED USER. ALL ACCESS ATTEMPTS TO THIS SYSTEM ARE AUDITED
##############################################################################
oracle's password:
Authentication successful.
Last login: Thu Oct 06 2005 12:58:58 from 10.128.133.2
You have mail.
You have mail.
ORACLE_SID = [oracle] ? MYDB1
oracle> sqlplus /
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Oct 6 13:59:42 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create user msr identified by msr temporary tablespace temp default tablespace users;
User created.
SQL> grant connect,resource to msr;
Grant succeeded.
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
oracle> exit
Connention to myhost1 closed.
oracle> cat exp_msr.par
userid=/
tables=msr1,msr2
log=msr.log
file=msr.pip
compress=n
rows=y
oracle> cat imp_msr.par
userid=system/password@SID
fromuser=OPS$ORACLE
touser=msr
tables=msr1,msr2
file=msr.pip
log=msr.log
oracle> mknod msr.pip p
oracle> nohup imp parfile= imp_msr.par &
[1] 23204
oracle> Sending output to nohup.out
oracle> exp parfile= exp_msr.par
Export: Release 9.2.0.6.0 - Production on Thu Oct 6 14:01:08 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table MSR1 1393 rows exported
. . exporting table MSR2 1394 rows exported
Export terminated successfully without warnings.
oracle>
oracle>
oracle>
oracle>
[1] + Done nohup imp parfile= imp_msr.par &
oracle>
oracle>
oracle> more nohup.out
Import: Release 9.2.0.6.0 - Production on Thu Oct 6 14:00:53 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by OPS$ORACLE, not by you
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$ORACLE's objects into MSR
. . importing table "MSR1" 1393 rows imported
. . importing table "MSR2" 1394 rows imported
Import terminated successfully without warnings.
oracle> ssh myhost1
##############################################################################
* * * * W A R N I N G * * * *
YOU MUST NOT ACCESS THIS SYSTEM, OR ATTEMPT TO ACCESS THIS SYSTEM, UNLESS
YOU ARE AN AUTHORISED USER. ALL ACCESS ATTEMPTS TO THIS SYSTEM ARE AUDITED
##############################################################################
oracle's password:
Authentication successful.
Last login: Thu Oct 06 2005 12:59:36 from 10.128.133.2
You have mail.
You have mail.
ORACLE_SID = [oracle] ? MYDB1
oracle> sqlplus msr/msr
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Oct 6 14:01:39 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> select count(*) from msr1;
COUNT(*)
----------
1395
SQL> select count(*) from msr2;
COUNT(*)
----------
1396
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
oracle>
