Export-Import Using UNIX Pipe

          In some scenarios where there is no disk space to hold the export dumps or when migrating schemas across different versions of Oracle, this script helps to do the export/import over the network or on the same server using the UNIX pipe.

In this example, data is exported from the Oracle Database instance named PROD to the instance named DEV. The export utility connects to the PROD database using the Net Service name, writes the export dump to a UNIX pipe, which is read by the import process that loads the data to the DEV database.

Create a file exp_imp.sh:

#!/bin/bash    export ORACLE_SID=odtest export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH    # make a unix pipe export PIPE=exp_imp_pipe mknod exp_imp_pipe p    # Start the Export and run it in background  - Dont omit the '&' at the end. exp scott/tiger@PROD file=exp_imp_pipe log=exp_PROD.log buffer=1024000 consistent=y OWNER=HR,APP direct=y statistics=none compress=n  &    # Start the Import imp scott/tiger ignore=y full=y file=exp_imp_pipe log=imp_DEV.log commit=y buffer=1024000 RESUMABLE=Y &

For big schemas, CONSISTENT=Y may not work as the export may fail with an ORA-01555 snapshot too old error, so you may need to revisit the parameter. RESUMABLE=Y at the import side can help to make the session ‘resumable’ in case the import hits any space issues on the target database. The export/import logs can be monitored to view the progress of the activity.
In this example the user Scott has enough privilege to exp/imp the data, make sure that you connect to the databases as a privileged user and needless to say, it is recommended to run the job in nohup mode to avoid issues in case connectivity to the server gets disturbed.