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>
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License