Row Count + DBMS_COMPARISON – Logical Replication

Another point from the business area was to validate if the data was the same, not only the number of rows. For this case, I decided to use DBMS_COMPARISON.

Here you can see an example of how to generate the scripts to validate:

select '
BEGIN
DBMS_COMPARISON.drop_comparison (
    comparison_name    => ''cutover_comp_bm'');
END;
/

BEGIN
  DBMS_COMPARISON.create_comparison (
    comparison_name    => ''cutover_comp_bm'',
    schema_name        => ''YOUR_SCHEMA'',
    object_name        => '''||table_name||''',
    dblink_name        => ''db_compare'',
    remote_schema_name => ''YOUR_SCHEMA'',
    remote_object_name => '''||table_name||''');
END;
/


SET SERVEROUTPUT ON
DECLARE
  l_scan_info  DBMS_COMPARISON.comparison_type;
  l_result     BOOLEAN;
  v_comparison_name    varchar2(100):= ''cutover_comp_bm'';
BEGIN
  l_result := DBMS_COMPARISON.compare (
                comparison_name => v_comparison_name,
                scan_info       => l_scan_info,
                perform_row_dif => TRUE
              );

  IF NOT l_result THEN
    DBMS_OUTPUT.put_line(v_comparison_name||'' Differences found. scan_id='' || l_scan_info.scan_id);
  ELSE
    DBMS_OUTPUT.put_line(v_comparison_name||'' No differences found.'');
  END IF;
END;
/

' 
from dba_tables
where owner='YOUR_SCHEMA';

Some tables were really big in this database(billion of rows) and for this situation, I found the parameter scan_mode.

You can use something like:

    scan_mode          => dbms_comparison.CMP_SCAN_MODE_RANDOM,
    scan_percent       => 0.001    

DBMS_COMPARISON cannot compare LOB columns and you can use this parameter to limit the columns to be compared:

column_list        => 'YOUR COLUMNS SEPARATED BY COMMA'

You can use this SQL to generate a column list:

SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id)
  FROM dba_tab_columns
 WHERE owner = 'YOUR_OWNER'
   and table_name='YOUR_TABLE'
   and data_type not like '%LOB%';

Thanks
Alex