Re: Migrate 200 Million of rows

Hi!

There is no such thing as direct patch import. Sql*loader can load data in database directly, but not import.

Also, definitely DO use database link to load your data. Database links can very well be used for transferring large amounts of data. I've migrated even TB sized databases that way. With spool you'll just make your life harder, plus you need to have this additional storage somewhere, plus you generate additional IO when writing to and reading from flat file (with a small exception of using SAN transfer, or rsh and pipes). I'd say that sqlplus spool command is not meant for big data unloading, it's slow in that sense.

If you're transferring between windows server you might want to use named pipes protocol for database link, it should be faster than TCP.

Tanel.

> do an export of the one table. Than a direct path import. Then do insert
/*+
> append */
> "Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message
> news:3722db.0307220653.2d4f17a3_at_posting.google.com...
> > Don't use a database link! This is intended for "small" quantities of
> > data! If I were you, I'd spool everything in a flat file, transfer the
> > file to the other database server, and do a "direct-load" SQL*Loader
> > import. My guess is that that would be the fastest way.
> >
> > Daniel
> >
> > > Hi,
> > >
> > > I would like to migrate 200 Million records. Furthermore my new table
> > > will have more fields that the old one. The new table will be in
> > > different database therefore a link will be used as well.
> > >
> > > I was thinking about:
> > > Strategy A)
> > > 1) create table ... as select * from
> > >
> > > with a join with temporary table to make the new table with new
> > > columns
> > >
> > > 2) create index, contraints etc
> > >
> > > Strategy B)
> > >
> > > 1) create table
> > >
> > > 2) create index and make it unusable (I have to check if this is
> > > possible in v8.0.5)
> > >
> > > 2) Insert into /*+ APPEND */ select * from
> > >
> > > 3) rebuild the index, constraints etc
> > >
> > > Could you please help me in the best strategy to perform this task?
> > >
> > > Do you have any more suggestions
>
>
Received on Tue Jul 22 2003 - 11:22:29 CDT