Bulk Insert into Oracle

can you clarify:
? are both on the same network?
? what is the context of this data migration?
? what is the key "event" to start a record to be copied?

Hi, Thanks for the quick response. Thats very fast.

1. Different segment.
2. Question not clear to me. There is a continuous flow of data into the MS SQL server and we need to copy over to Oracle.
3. The data pumped into MS SQL non-stop at the rate of 10,000 records / sec and our job is to copy these records min at that rate.

Regards
PSec

>1. Different segment.
but they can "see" each other, meaning that you can ping/telnet/ftp/whatever from one to the other?

>? what is the context of this data migration?
so, you get +-10K records/sec created in sql server, and want to see them in oracle copied at the same speed? (aka real-time), is that correct?

yes the SQL Server database can be connected from Source segment.
10K records/sec created in the SQL Server can be bulk loaded to Oracle as long as the time to load can catch up with the insertion (to the SQL Server) rate.

I think you can use linked server option of MS SQL server

I doubt there is any simple solution to copying 10k records per second in between databases without some sort of bulk load / direct path technology. Depending on your system, you might not get 10k rows/sec from INSIDE the Oracle database, just by doing "insert into ... select * from ..."

Standard SQL, ODBC and/or ADO loads which use standard data / SQL layer will be more along the lines of 1-2k per second, in my experience, but of course this is subjective, since the record structure, the disk speed and the CPU will affect performance. My point is, I doubt taking any standard approach will work. Maybe you can approach this number with parallel inserts, but then you have to partition the Oracle table to use the direct path (append) option.

I'm certain the Oracle-SQL Server gateways, linked servers, etc. will not approach this speed, as they do not use direct path by default, if at all.

Is there a bulk dump tool available in SQL Server? If so, dumping it to a file format, then loading with SQL Loader might be an option.

600,000 records per minute is heavy. Is it non-stop 24x7? Do the records have primary keys or require any Oracle sequences? This rate is doable by SQL Loader, or perhaps direct path inserts, but not by standard, single threaded SQL inserts, using standard platter disks.

Thank all of you for the inputs.