Oracle超大规模表分块导出问题：分区/子分区表适配异常

1. 关联条件错误：分区/子分区表中，子分区的segment_name是父分区的名称，而非表名，原查询中e.segment_name = :object_name的关联逻辑会丢失子分区的extents数据。
2. RowID范围计算错误：分区/子分区表的每个子分区拥有独立的data_object_id，原导出查询使用表的data_object_id生成RowID范围，会导致漏读或重复读取子分区数据。

该语句兼容普通表、分区表、子分区表，按每个独立segment（子分区/分区/表）的extents进行分块：

SELECT
    subpart_data_obj_id AS data_object_id,
    e.file_id,
    e.relative_fno,
    file_batch,
    subpartition_name,
    MIN(e.block_id) AS start_block_id,
    MAX(e.block_id + e.blocks - 1) AS end_block_id,
    SUM(e.blocks) AS blocks
FROM (
    -- 处理子分区表
    SELECT
        tsp.data_object_id AS subpart_data_obj_id,
        e.file_id,
        e.relative_fno,
        e.block_id,
        e.blocks,
        tsp.subpartition_name,
        -- 按每个子分区的总块数拆分，将/1改为N可分成N块
        CEIL(SUM(e.blocks) OVER (PARTITION BY tsp.data_object_id, e.file_id ORDER BY e.block_id ASC) / 
             (SUM(e.blocks) OVER (PARTITION BY tsp.data_object_id, e.file_id) / 1)) AS file_batch
    FROM dba_extents e
    JOIN dba_tab_subpartitions tsp
        ON e.owner = tsp.table_owner
        AND e.segment_name = tsp.partition_name
        AND e.partition_name = tsp.subpartition_name
    WHERE tsp.table_owner = :owner
      AND tsp.table_name = :object_name
    UNION ALL
    -- 处理普通分区表（无子分区）
    SELECT
        tp.data_object_id AS subpart_data_obj_id,
        e.file_id,
        e.relative_fno,
        e.block_id,
        e.blocks,
        tp.partition_name AS subpartition_name,
        CEIL(SUM(e.blocks) OVER (PARTITION BY tp.data_object_id, e.file_id ORDER BY e.block_id ASC) / 
             (SUM(e.blocks) OVER (PARTITION BY tp.data_object_id, e.file_id) / 1)) AS file_batch
    FROM dba_extents e
    JOIN dba_tab_partitions tp
        ON e.owner = tp.table_owner
        AND e.segment_name = tp.partition_name
        AND e.partition_name IS NULL
    WHERE tp.table_owner = :owner
      AND tp.table_name = :object_name
    UNION ALL
    -- 处理普通表（无分区）
    SELECT
        o.data_object_id,
        e.file_id,
        e.relative_fno,
        e.block_id,
        e.blocks,
        NULL AS subpartition_name,
        CEIL(SUM(e.blocks) OVER (PARTITION BY o.data_object_id, e.file_id ORDER BY e.block_id ASC) / 
             (SUM(e.blocks) OVER (PARTITION BY o.data_object_id, e.file_id) / 1)) AS file_batch
    FROM dba_extents e
    JOIN dba_objects o
        ON e.owner = o.owner
        AND e.segment_name = o.object_name
        AND e.partition_name IS NULL
        AND o.subobject_name IS NULL
    WHERE o.owner = :owner
      AND o.object_name = :object_name
)
GROUP BY subpart_data_obj_id, file_id, relative_fno, file_batch, subpartition_name
ORDER BY subpart_data_obj_id, file_id, relative_fno, file_batch;

使用分块查询返回的data_object_id（对应子分区/分区/表的真实对象ID）生成RowID范围：

SELECT /*+ NO_INDEX(t) */ COLUMN_NAMES, '<file_id>_<file_batch>' AS data_chunk_id
FROM OWNER.OBJECT_NAME t
WHERE ROWID BETWEEN DBMS_ROWID.ROWID_CREATE(1, :data_object_id, :file_id, :start_block_id, 0)
                AND DBMS_ROWID.ROWID_CREATE(1, :data_object_id, :file_id, :end_block_id, 32767);

• 调整分块大小：将分块查询中的/1改为目标分块数N，可控制每个分块的块数，避免单个分块过大导致会话超时。
• 数据完整性验证：对每个分块执行COUNT(*)，与dba_tab_subpartitions.num_rows（子分区）、dba_tab_partitions.num_rows（分区）或dba_tables.num_rows（普通表）对比，确保行数匹配。
• 一致性保障：若导出期间有DML操作，可加读锁（LOCK TABLE ... IN SHARE MODE）或使用闪回查询（AS OF TIMESTAMP）避免数据不一致。

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