Re: Moving data - EXPORT or COPY or SQLLOADER ???

In article <01bc9808$4a260fe0$1033549e_at_w-philh.uk.sequent.com>, Phil Hoggins <philh_at_sequent.com> wrote:   

> > Problem :
> > ----------
> >
> > Move 2 tables with > 1 million rows from one machine to another.
> > Exports files would be > 2 gig ( unix file limit )
> >
> > Alternatives I considered:
> > --------------------------
> >
> > 1. using COPY command over SQL*Net linking both machines/databases.
> > 2. Moving subsets of data thru flat files.
> > 3. ????
> >
> > My Questions:
> > --------------
> >
> > 1. Can anyone who has had prior experience in the above advice on the
> > best alternative / strategies they adopted ?
> >
> >
> In a DSS environment moving large volumes of data is a common practice.
>
> I tend to favour dumping the data into OS files to then be used be
> SQL*Loader as this is the fastest way to get data back into a database as
> SQL*Loader can run parallel, direct and unrecoverable. I've managed to
> load 2million rows of data into a table in 4 minutes and build indexes in
> another 6 minutes, total load time 10 mins.
>
> The only problems you face is how to unload the data. SQL*Plus is one
> option but parhaps a little slow. Look around at some of the Oracle sites
> on the net for a C program to unload the data, this would be more efficent.
>
> on another point if you were to use export, you could perform your export
> via a pipe and compress it reducing the size of the dump file. The import
> process can be done in the same way, uncompress the file via a pipe to the
> import utility. I think there are examples of this in the Oracle Magazine
> Code Depot.

To address the issue of > 2GB add a where clause to reduce file size. or try using NAMED pipes just like the export. It is system dependent but you should be able to get sql*loader or import to work on > 2GB of data by using NAMED pipes. AFAIK neither sql*loader or import will work with command line pipes "|". On UNIX look at the mkfifo command.   Received on Sun Jul 27 1997 - 00:00:00 CDT