Alternative to EXP/IMP

From: Andy Johnson <AJohnson_at_Armature.com>
Date: Thu, 11 Oct 2001 14:19:42 +0100
Message-ID: <783866CAB183D211883F0090271F46C208A2CBB5@COW>

Hi ron...

You could always try and use the direct path insert method using the /* append */ hint this will generate no rollback and is pretty fast although I cannot guarantee it's speed across db versions.

                e.g

		insert /* append */ into slave_table(cola,colb,colc)
		select cola,colb,colc from master_table

will do a fast insert. Or you could try ctas with unrecoverable option or sqlloader..

hope any of these help...

rgds

andy

		-----Original Message-----
		From:	angelo.oliveri_at_iname.com (Angelo)
[mailto:angelo.oliveri_at_iname.com]
		Posted At:	11 October 2001 14:05
		Posted To:	server
		Conversation:	Alternative to EXP/IMP
		Subject:	Re: Alternative to EXP/IMP

		Hi Ron 

		I saw your reply about how you moved 10G of data from
one database to
		another in just 2 hours. However, you didn't give any
clues how
		exactly you did it. We have a 130G database on oracle
805 (solaris
		2.6) that we are trying to import data to a new database
running 9i
		(solaris 8). Both systems are linked together by 100Mbps
ethernet, and
		database links have been created between both systems.
We have tried
		export and import which is slow, and tried a pl/sql
copy/commit type
		process, which is also much slower than the results you

quoted.

                You mentioned using direct path for reading and writing rows, which I

                thought was a feature of either exp or imp, but not relevent to

                dynamic sql, and I'm not sure what parallel query has to do with it

                thanks

                angelo

                devnull_at_ronr.nl (Ronald) wrote in message news:<67ce88e7.0109260625.6d558c65_at_posting.google.com>...

> rshea_at_my-deja.com (Richard Shea) wrote in message
news:<43160f6f.0109260043.61adffb2_at_posting.google.com>...

> > Hi - I'm looking at alternatives to EXP/IMP when
moving a schema/data

> > from one box to another (this is because I've got
the schema/data on a

> > higher version of Oracle then the target machine
has, 8.1.7 is trying

> > to go to 8.1.6).
>
> Hi Richard,
>
> I think the method depends a bit on taste. Last
weekend I migrated a

> 7.3.4 database from NT to 8.1.6.3 on Solaris with a
smart piece of

> dynamic sql. Doing this way you can read the tables
using direct path

> and also insert the rows with direct path, making it
very quick. It

> took 2 hours to copy a 10G database this way (using
parallel query on

> a 2 node solaris ops database). This works fine as
long as you don't

> have LONG or LONG RAW datatypes.
>
> My reason for this method was the abillity to move
tables to different

> tablespaces as in the source database.
>
> Ronald.
> -----------------------
> http://ronr.nl/unix-dba
Received on Thu Oct 11 2001 - 08:19:42 CDT