We’ve created a stored procedure to pull in full copies of some Oracle tables with a Linked Server.
Over the course of 11 hours, the procedure correctly populated the first 11 tables but failed with this error on table 12:
Msg 7399, Level 16, State 1, Procedure Refresh_From_Oracle, Line 45 The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Procedure Refresh_From_Oracle, Line 45 Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE".
Is there anything there that gives a clue as to why the procedure failed when it got to table 12? Or is there a limit to how long a procedure can run, or a query can run against a linked server?
All tables except 12 and 13 have <2,000,000 rows, and 12 and 13 have about 70,000,000 each.
Here is the stored procedure:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Jack Douglas -- Create date: 2016-10-07 -- Description: Truncates and re-populates UAT copies of Oracle data -- ============================================= ALTER PROCEDURE [dbo].[Refresh_From_Oracle] AS BEGIN SET NOCOUNT ON; TRUNCATE TABLE dbo.TABLE1; INSERT INTO dbo.TABLE1 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE1; … TRUNCATE TABLE dbo.TABLE12; INSERT INTO dbo.TABLE12 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE12; TRUNCATE TABLE dbo.TABLE13; INSERT INTO dbo.TABLE13 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE13; END
The problem turned out to be on the Oracle side:
Tue Oct 18 05:41:17 2016 ORA-01555 caused by SQL statement below (Query Duration=30945 sec, SCN: 0x0aee.4682f3e7): Tue Oct 18 05:41:17 2016
It’s the classic ‘Snapshot too old’ issue with a long-running transaction and MVCC.