Sqoop Direct Oracle mode [from Sqoop 1.4.5 (Quest)]

sqoop_direct_oracle

SELECT data_object_id,
  file_id,
  relative_fno,
  file_batch,
  MIN (start_block_id) start_block_id,
  MAX (end_block_id) end_block_id,
  SUM (blocks) blocks
FROM
  (SELECT o.data_object_id,
    e.file_id,
    e.relative_fno,
    e.block_id start_block_id,
    e.block_id + e.blocks - 1 end_block_id,
    e.blocks,
    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) / 25)) file_batch
  FROM dba_extents e,
    dba_objects o,
    dba_tab_subpartitions tsp
  WHERE o.owner         = 'SHOPS_WEB'
  AND o.object_name     = 'AUCTION_RULE'
  AND e.owner           = 'SHOPS_WEB'
  AND e.segment_name    = 'AUCTION_RULE'
  AND o.owner           = e.owner
  AND o.object_name     = e.segment_name
  AND (o.subobject_name = e.partition_name
  OR (o.subobject_name IS NULL
  AND e.partition_name IS NULL))
  AND o.owner           = tsp.table_owner(+)
  AND o.object_name     = tsp.table_name(+)
  AND o.subobject_name  = tsp.subpartition_name(+)
  AND
    CASE
      WHEN o.object_type='TABLE SUBPARTITION'
      THEN tsp.partition_name
      ELSE o.subobject_name
    END IN (:partition_list)
  )
GROUP BY data_object_id,
  file_id,
  relative_fno,
  file_batch
ORDER BY data_object_id,
  file_id,
  relative_fno,
  file_batch;

Get partition blocks details to determine chunks for multiple workers (used in Sqoop), getOracleDataChunksExtent.

Actually, on large databases, dba_extents table SELECT took too much time.

After that Sqoop uses table blocks details to map chunks to parallel import workers
SELECT *
FROM sometable
WHERE rowid >= dbms_rowid.rowid_create(1, 893, 1, 279, 0) AND
rowid <= dbms_rowid.rowid_create(1, 893, 1, 286, 32767)