Re: fastest method to copy data between databases

In article <32792ABA.36C_at_pcc.edu>, dpelinka_at_pcc.edu says...

>I'm setting up an Oracle database for a data warehouse and would like to
>know what is the fastest way to move (replicate) data. I can't use
>snapshots against a remote view of multiple tables under 7.1.6 due to an
>Oracle bug. I've tried copy (copy from user_at_remote create local_table
>using select * from view) but it's taking 1 hour to move 1,000 - 10,000
>records. Table extents are few. The remote views are complex, but the
>select is fast in the remote database. Copying a single table is fast
>too.

What is the Oracle 7.1.6 bug?

We're running 7.1.6 & are using complex snaphots against remote databases without any problems. Actually I tell a lie, one snapshot did have a problem as it was using user defined functions without RPNS purity (I posted about this a coupel of days ago). Having got rid of the offending code so the functions _can_ use RPNS purity, they work fine. Maybe that is the cause of your problem?

As Oracle provide support for snapshots, you may as well use it.

If not, why not run a query on your remote machine into a flat file; copy or remotely mount the file, then just Sql*Load it into your warehouse using direct path loading?

Cheers.
Martin. Received on Thu Nov 07 1996 - 00:00:00 CST