SQL Server 与 Oracle 跨库操作、高并发场景实现方案(初始方案)

项目需求：将sqlserver中临时表sqlservertemp_table的COMPANY,OGB04,OGA01,OGA03数据传入到已建立链接的oracle实例名ds中，便于从oracle中提取数据时，读取表里数据限定查询条件，减少无效资料的读取到sqlserver。

我的实现步骤是：

1.在Oracle先建立要传入参数一致的全局表结构：

CREATE GLOBAL TEMPORARY TABLE your_oracle_temp_table (
    COMPANY VARCHAR2(50),
    OGB04   VARCHAR2(100),
    OGA01   VARCHAR2(100),
    OGA03   VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;

2.SQLSERVER中，传递前先清空 Oracle 临时表

 EXEC ('DELETE FROM your_oracle_temp_table') AT DS; 

3.数据量大时采取分批插入，防止锁表发生。

DECLARE @BatchSize INT = 500;

WHILE (EXISTS(SELECT 1 FROM #Params))
BEGIN
INSERT INTO OPENQUERY(DS, 'SELECT COMPANY,
OGB04,
OGA01 ,
OGA03 FROM your_oracle_temp_table')
SELECT TOP (@BatchSize) COMPANY,
OGB04,
OGA01 ,
OGA03
FROM #Params;

DELETE TOP (@BatchSize)
FROM #Params;

-- 可选：释放事务资源
WAITFOR DELAY '00:00:01'; -- 控制频率 
END

-- 删除临时表 

DROP TABLE #Params;

我的想法存在的问题与风险：

1. 跨库操作使用 OPENQUERY + EXEC AT DS

• OPENQUERY 是一种链接服务器查询方式，但不支持参数化或动态语句。
• EXEC ('DELETE FROM your_oracle_temp_table') AT DS; 是可行的，但每次执行都是一次远程调用，性能较低。
• 插入操作使用 INSERT INTO OPENQUERY(...)，虽然可以工作，但效率低、易出错。

2. Oracle 全局临时表 (GTT) 的问题

• 使用 ON COMMIT PRESERVE ROWS 意味着数据会保留到事务结束。
• 如果多个用户/线程同时操作这张 GTT 表，会出现数据污染（不同用户的插入互相干扰），因为默认是会话共享的。
• 无法天然支持多并发。

3. 清空表逻辑的风险

 EXEC ('DELETE FROM your_oracle_temp_table') AT DS; 

 如果多个请求同时执行此语句，可能导致所有会话的数据都被清空，造成数据丢失或错误。

4. WAITFOR DELAY 控制频率

 WAITFOR DELAY '00:00:01'; 

这是为了降低插入频率，防止短时间内大量插入导致资源竞争或锁等待。

对于性能优化来说，这并不是一个理想的方法。

三、改进后的实现步骤

1. 避免使用全局临时表，改用“带唯一标识”的普通表

建议在 Oracle 创建如下结构：

CREATE TABLE oracle_temp_table (
    session_id VARCHAR2(50),  -- 标识本次操作的唯一ID
    COMPANY    VARCHAR2(50),
    OGB04      VARCHAR2(100),
    OGA01      VARCHAR2(100),
    OGA03      VARCHAR2(100)
);

SQL Server 端插入时带上唯一标识（如 GUID）临时表可增加NAME前缀标识方便检索

DECLARE @SessionID VARCHAR(50) = NEWID();
SET @SessionID = 'NAME_'+REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', '_');
WHILE (EXISTS(SELECT 1 FROM #Params))
BEGIN
    INSERT INTO OPENQUERY(DS, 'SELECT session_id, COMPANY, OGB04, OGA01, OGA03 FROM oracle_temp_table')
    SELECT @SessionID, COMPANY, OGB04, OGA01, OGA03
    FROM #Params;

    DELETE TOP (@BatchSize) FROM #Params;
END

这样每个请求都有独立的数据空间，不会互相干扰。

 

如要经常使用 T-SQL 实现，可封装为存储过程

CREATE PROCEDURE Sync_Params_To_Oracle
AS
BEGIN
    DECLARE @SessionID VARCHAR(50) = NEWID();
    SET @SessionID = 'NAME_'+REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', '_');
    -- 清空本 Session 的历史数据
    EXEC ('DELETE FROM oracle_temp_table WHERE session_id = session_id = ''' + @SessionID + 
　　 '''' ) AT DS;

　　DECLARE @BatchSize INT = 500;

    WHILE EXISTS (SELECT * FROM #Params)
    BEGIN
        INSERT INTO OPENQUERY(DS, 'SELECT session_id, COMPANY, OGB04, OGA01, OGA03 FROM oracle_temp_table')
        SELECT TOP (@BatchSize) @SessionID, COMPANY, OGB04, OGA01, OGA03
        FROM #Params;

        DELETE TOP (@BatchSize) FROM #Params;
    END

    -- 返回 Session ID，用于后续 Oracle 查询
    SELECT @SessionID AS SessionID;
END