Is there a limit on query time for an Oracle linked server?

Posted on

Question :

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

Answer :

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.

Leave a Reply

Your email address will not be published.