启用EnableStreaming与小BatchSize时SqlBulkCopy仍内存占用过高的问题排查——PowerShell从Oracle批量导入SQL Server场景

老兄，你的问题核心其实很明显——你先用OracleDataAdapter.Fill($dtbl)把Oracle查询的所有结果一次性塞进了内存里的DataTable！这时候不管你给SqlBulkCopy设置EnableStreaming=$true还是BatchSize=1000，都是从内存里的完整数据集读数据，自然会占满所有可用内存，而且NotifyAfter也不会触发——因为数据已经全在内存里了，根本不存在分批读取的过程。

EnableStreaming的作用是让SqlBulkCopy直接从IDataReader流式读取数据，跳过“全量加载到内存”的步骤，所以你得从数据源就开始做流式处理，而不是先把所有数据捞到内存里。

我们需要把OracleDataAdapter替换成OracleCommand+OracleDataReader，直接用流式读取的方式把数据传给SqlBulkCopy，这样才会真正实现分批加载、低内存占用，同时NotifyAfter也能正常生效。

修改后的完整代码 #

$current = Get-Date 

# 加载Oracle托管驱动
Add-Type -Path "D:\oracle\product\12.1.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"; 

# 定义Oracle连接字符串
$oracleConnStr = "cstr" 
# Oracle表查询语句
$qry = " SELECT ID,CREATEDT,MODIFIEDDT FROM MYTABLE WHERE ISSYNTHETIC=0 AND VALIDFROM >= TO_Date('2019-01-01','yyyy-mm-dd') "; 
# 源列与目标列映射关系（修复了你原来漏写的MODIFIEDDT目标列）
[hashtable] $columnMapping = @{
    'ID' = 'ID';
    'CREATEDT' = 'CREATEDT';
    'MODIFIEDDT' = 'MODIFIEDDT'
}; 

# 初始化Oracle连接和命令
$oracleConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($oracleConnStr)
$oracleCmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($qry, $oracleConn)

# 设置FetchSize，控制每次从Oracle读取的行数（按需调整，这里和BatchSize保持一致）
$oracleCmd.FetchSize = $oracleCmd.RowSize * 1000

# SQL Server目标连接字符串
$sqlConnStr = "cstr"; 

try {
    # 打开Oracle连接
    $oracleConn.Open()

    # 获取流式数据读取器（CommandBehavior.CloseConnection：读取完成后自动关闭Oracle连接）
    $dataReader = $oracleCmd.ExecuteReader([System.Data.CommandBehavior]::CloseConnection)

    # 初始化SqlBulkCopy
    $sqlbc = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConnStr)
    $sqlbc.BatchSize = 1000
    $sqlbc.EnableStreaming = $true
    $sqlbc.NotifyAfter = 1000
    $sqlbc.DestinationTableName = "DWHODS.MYTABLE"

    # 添加列映射
    foreach ($sourceCol in $columnMapping.Keys) {
        $destCol = $columnMapping[$sourceCol]
        $colMap = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping($sourceCol, $destCol)
        $sqlbc.ColumnMappings.Add($colMap) | Out-Null
    }

    # 注册SqlRowsCopied事件，实现实时进度通知
    $sqlbc.add_SqlRowsCopied({
        param($sender, $e)
        Write-Host "已完成复制 $($e.RowsCopied) 行数据"
    })

    # 流式写入数据（核心：直接从DataReader读数据，不占满内存）
    $sqlbc.WriteToServer($dataReader)

} finally {
    # 确保所有资源安全释放，避免连接泄漏
    if ($null -ne $dataReader) { $dataReader.Close() }
    if ($null -ne $sqlbc) { $sqlbc.Close() }
    if ($null -ne $oracleConn -and $oracleConn.State -eq [System.Data.ConnectionState]::Open) { $oracleConn.Close() }
}

$end = Get-Date 
$diff = New-TimeSpan -Start $current -End $end 
Write-Output "导入总耗时: $diff"

关键改进点说明 #

1. 改用OracleDataReader流式读取：不再把所有数据加载到内存的DataTable，而是直接从Oracle数据库分批读取数据，内存峰值会大幅降低。
2. 设置FetchSize：控制每次从Oracle服务器拉取的行数，避免单次读取过多数据，进一步优化内存占用。
3. 实现SqlRowsCopied事件：现在NotifyAfter=1000会正常生效，每复制1000行就会输出进度，你可以实时看到复制状态。
4. 资源安全释放：用try/finally块确保所有数据库连接、读取器、SqlBulkCopy对象都能正确关闭，避免资源泄漏。
5. 修复映射错误：你原来的映射里'MODIFIEDDT'没有写目标列，我已经补上了，否则会导致列映射失败。

额外优化建议 #

• 如果你的Oracle查询结果特别大，可以适当调小FetchSize的值（比如设为500行），进一步降低内存峰值。
• 确保SQL Server的目标表没有过多的索引或触发器，否则会拖慢批量复制的速度。
• 保持SqlBulkCopy的BatchSize和OracleCommand的FetchSize行数一致，这样每读取一批就写入一批，内存占用更稳定。

内容的提问来源于stack exchange，提问作者OpenStove