Re: exporting directly from one server to another

  • From: Tanel Põder <tanel.poder.003@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 24 May 2004 19:28:23 +0300

Btw, you should test out the following approach:

1. export w. rows=n

2. import w. constraints=n indexes=n

3. use a database link to transfer all of your data to new database (see
script below)

4. create index creation scripts from exportfile, modify it to use nologging
options, create indexes with it

5. import w. constraints=y (note that some "implicit indexes" may also be
created in this stage)

The script:

set linesize 300

set pagesize 0

set feedback off

set trimspool on

spool /tmp/apps_insert.sql

select 'insert /*+ APPEND NOLOGGING */ into '

   || owner || '.' || table_name

   || ' select * from ' || owner || '.' || table_name || '@dblink;'

from dba_tables

where owner in ('AP', 'APPLSYS', 'APPS', 'AR', 'GL', 'JE')

order by owner;

spool off


The nologging option as a hint works starting from 9i, otherwise you should
use alter commands to set a table temporarily to nologging.

This approach can be faster, since it doesn't require regular array inserts,
like import does - regular inserts are always logged.


----- Original Message ----- 
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, May 21, 2004 10:37 PM
Subject: RE: exporting directly from one server to another

> From memory, it's been a while since I did this,=20
> it goes something like this:
> login to destination server:
> mknod destination_pipe p
> imp file=3Ddestination_pipe <other import options>
> (now import is blocking on the pipe, waiting for data to arrive.
> It will patiently wait till it starts receiving data.)
> login to source server:
> mknod source_pipe p
> cat source_pipe | ssh destination_server cat >> destination_pipe
> (Now the cat and the ssh are blocking on input to the source_pipe.)
> Finally:
> exp file=3Dsource_pipe <other export options>
> And you should see the export take off on the source box and
> a few seconds later, see an import take off on the destination box.

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: