Re: Alternative to EXP/IMP

Angelo wrote:
>
> 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

I have done this type of thing similar to Ron's method. In my case, I used parallelism for the insert commands. I moved about 100G in 8 hours between 2 Solaris 2.7 boxes running 8.1.6 throgh database links. To gain the proper performance, there were no indexes created on the target instance, and no logging either.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.