We have SQL server and Oracle server, and have configured a linked server from SQL Server to Oracle.
I'm trying to insert 10,000 rows from SQL Server into a table in Oracle.
When I insert 100,000 rows it takes 2 minutes, when I try to insert 1 million rows it can run for more than 10 hours and at the end of this I need to stop the insert because nothing is happening.
Could you please give me answer why it takes so long for 1 million rows? It should take approximately 1 hour if it runs the same speed as 10k rows.
What I'm trying to do:
INSERT INTO [linksrvname]..[schemaname].[oracletablename ] ( column1, column2 ) SELECT top 10000 column1, column2 FROM sqlservertable ;
...completed in few seconds. After that I have deleted all rows from the Oracle table. When I'm trying to insert 100,000 it is completed in few minutes. When I'm trying to insert 500,000 it is running for a few hours now; SQL Server hangs with
OLEDB wait type. Unfortunately, I don't have access to the Oracle server.
What could the problem be?