Export/Import on the fly: The simple way

In a my previous post, I have shown how to exporting and importing using two different pipes: one for each server. One pipe on source server and another one on the target server.

There is a more simple way to do the same thing: just using one pipe.

In this case you have to create one pipe on a target server (for example) and use it to write and read the data.

Configuring the target

[oracle@trc_hst]$ mknod /tmp/ExpImp.pipe p 
[oracle@trc_hst]$ nohup imp parfile=/tmp/Import.par \
                        >  /tmp/Import.nohup 2&>1 &

[oracle@trc_hst]$ time nohup exp parfile=/tmp/Export.par \
                             > /tmp/Export.nohup 2>&1 &

Let see the parameter files. Keep in mind that this is just an example where I exported only two tables.

[oracle@trc_hst]$ cat /tmp/Export.par

USERID=src_usr/src_pwd@src_db
FILE=/tmp/ExpImp.pipe
LOG=/tmp/Export.log
TABLES=(T1, T2)
DIRECT=Y
CONSISTENT=Y
ROWS=Y
INDEXES=N
BUFFER=4000000


[oracle@trc_hst]$ cat /tmp/Import.par

USERID=trc_usr/trc_pwd 
FILE=/tmp/ExpImp.pipe 
LOG=/tmp/Import.log
BUFFER=5000000
RECORDLENGTH=5000000
IGNORE=Y 
INDEXES=N 
FEEDBACK=10000 
TABLES=(T1, T2)

As you see, in the Export.par file the USERID is referenced using SQL*Net (@src_db).

Configuring the source

Nothing to do


Update

- 2017/July/25 -
In my example I exported (using "exp" binary, not "expdp") from oracle 12.1.0.2 and imported (using "imp" obviously) in Oracle 10.2.0.4, using the client of the 10.2.0.4. Anyway, there is a compartibility you have :to check. On MOS you can read following Doc ID: "Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] (Doc ID 132904.1)".

From "Export From 11g or 12c using EXP Utility Version 9iR2 Produces Corrupt Export Dump (Doc ID 550740.1)", Doc ID:

You need to export data from 11g or 12c and import into 9iR2. For this reason, the exp utility version 9iR2 is used to extract data from 11g or 12c. Exp utility version 9iR2 internally uses the dictionary view EXU9DEFPSWITCHES to get information about some parameters like PLSQL_COMPILER_FLAGS and NLS_LENGTH_SEMANTICS. The parameter PLSQL_COMPILER_FLAGS doesn't exist in 11g or 12c any longer and so the view EXU9DEFPSWITCHES returns unhandled 0 rows.