Problem:

Oracle CDC client stage shows only one record when previewing.

Analysis:

Oracle CDC Stage preview is unique from other stage previews that it does not treat a value of 0 or -1 (as forever) when setting the “Batch wait time (secs)”.  According to its mechanics, this is expected and by design. Preview Batch Size is only a limit on the records returned by the pipeline, but, as batches takes precedence.

Mitigation:

Raise “Batch wait time (secs)” in JDBC Settings to a non-zero value.

Lastly, In regards of best practices with Oracle CDC pipelines

A sensible value would be around 15 to 30 seconds. This way you do not produce too small of batches that will impact overall performance.