Re: Export/Import data thru pipe from prod to test server

Dianna
You wrote:

"We're AIX 5.2 Oracle 9206 and need to export data from prod to our test instance for refresh.I thought I had read where I could use pipes and export/move data to test box/import all at the same time thus eliminating disk space for the export file. Any thoughts for doing this? Thanks."

Yes, this is a well known technique.

Before you start you need to clear down the target schema(s) - ie drop all the objects in them (if you want a complete refresh), or clear all the data (if it is a data-only transfer - ie if the structure is unchanged; don't forget you'll probably need to disable constraints too).

Then the basic sequence is, as best I remember offline: #############
# create a fifo

 rm -f mypipe # just to make sure there's not a real file there

 mknod mypipe p # the p identifies it is a pipe you want to make

# start a background process importing from the fifo

 imp target/password file=mypipe fromuser=<source> touser=<target> <other parameters> &

# start a background process exporting to the fifo

exp source/password file=mypipe <other parameters> &

# wait for them to finish

wait

##############

1. If this is going to take a while, consider using nohup for both the background commands (then it won't get terminated if you log off)
2. You'll see the logging from both processes intermingled - you can redirect the stdout to separate imp/exp log files as required
3. Of course you can parallelise this by exporting table subsets in multiple (exp|imp) pairs - but don't forget that table export doesn't include code etc...

HTH Regards Nigel

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l