Re: exporting directly from one server to another

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Mon, 24 May 2004 19:28:23 +0300
Message-ID: <01da01c441ac$22746410$8d879fd9@porgand>

Btw, you should test out the following approach:

export w. rows=n
import w. constraints=n indexes=n
use a database link to transfer all of your data to new database (see script below)
create index creation scripts from exportfile, modify it to use nologging options, create indexes with it
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

exit

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.

Tanel.

Original Message ----- From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> To: <oracle-l_at_freelists.org> 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: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon May 24 2004 - 11:25:44 CDT