Skip to content

How to Compare Two Databases After Migration (2/3) - Comparing Row Counts by Leveraging Statistics

  • by
How to Compare Two Databases After Migration (1/3) - Comparing Initialization Parameters
After a data migration, you might want to compare the number of rows of tables between the source and target databases. Since the namespace of tables in the source database could be changed in the target database, so we should not to compare whole databases to avoid table-name collisions.

What I will demonstrate in this article is a sample stored procedure to compare tables within a same namespace, which means two schemas come from the source and target database will be involved, and they will become as input parameters of the following procedure.

During comparison, the procedure will leverage the table statistics gathered by optimizer to compare the number of rows of a table, rather than to count and scan an entire table, which means, it will retrieve NUM_ROWS from DBA_TABLES at both databases first, if the values do not match, then it will actually count the table for further confirmation. After every comparison, the procedure will output the result on the screen.
  1. Gather schemas statistics manually in the target database.
  2. Some statistics could be empty after a data migration, especially in the target database, but we can do it manually by executing a database-level procedure DBMS_STATS.GATHER_DATABASE_STATS, and it will take a considerable time to complete in a terabyte-grade database. In this case, we execute a schema-level procedure called DBMS_STATS.GATHER_SCHEMA_STATS instead for saving time and resource.
    [oracle@targetserver ~]$ sqlplus system@target_db
    ...
    SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');

    PL/SQL procedure successfully completed.

    We could benefit from the gathered statistics, it facilitates the comparing procedure to quickly compare a table. But this is an optional step to my design. Because the procedure will actually count the table when two NUM_ROWS do not match.
  3. Create a database link to the source database in the target database.
  4. SQL> CREATE DATABASE LINK SOURCE_DB_LINK
      2  CONNECT TO system IDENTIFIED BY system_password
      3  USING 'source_db';

    Database link created.

  5. Grant a system privilege to system in the target database.
  6. Before editing the stored procedure, you should grant SELECT ANY DICTIONARY to the user who will create the procedure. In this case, we use "system" to create and execute the procedure.
    SQL> GRANT SELECT ANY DICTIONARY TO system;

    Grant succeeded.

  7. Edit the stored procedure in the target server.
  8. [oracle@targetserver ~]$ vi compare_table_rowcount.sql
    ...
    SET SERVEROUTPUT ON;
    ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

    CREATE OR REPLACE PROCEDURE SYSTEM.COMPARE_TABLE_ROWCOUNT(p_source_schema VARCHAR2, p_target_schema VARCHAR2 DEFAULT NULL) AUTHID CURRENT_USER
    IS
     v_source_schema VARCHAR2(30) := p_source_schema;
     v_target_schema VARCHAR2(30) := p_target_schema;
     TYPE t_rec IS RECORD (OWNER VARCHAR2(30), TABLE_NAME VARCHAR2(30), NUM_ROWS NUMBER);
     TYPE t_tab IS TABLE OF t_rec INDEX BY PLS_INTEGER;
     v_tab t_tab;
     v_time VARCHAR2(18);
     v_sql VARCHAR2(200);
     v_tname VARCHAR2(30);
     v_tcount NUMBER;
     v_rowcount1 NUMBER;
     v_rowcount2 NUMBER;
     v_found NUMBER;
     v_match VARCHAR2(5);
     j NUMBER;

    BEGIN
     IF v_target_schema IS NULL THEN
       v_target_schema := v_source_schema;
     END IF;

     SELECT OWNER, TABLE_NAME, NVL(NUM_ROWS,0) BULK COLLECT INTO v_tab FROM DBA_TABLES@SOURCE_DB_LINK WHERE NESTED='NO' ORDER BY TABLE_NAME;
     SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') INTO v_time FROM DUAL;
     DBMS_OUTPUT.PUT_LINE('==================== Begin time : '||v_time||' ====================');
     DBMS_OUTPUT.PUT_LINE('Source Schema : '||v_source_schema);
     DBMS_OUTPUT.PUT_LINE('Target Schema : '||v_target_schema);
     DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
     DBMS_OUTPUT.PUT_LINE(LPAD('#',3,'#')||' '||RPAD('Table Name',20)||LPAD('SOURCE ROWS',14)||LPAD('TARGET ROWS',14)||LPAD('MATCHED?',10));
     DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
     j := 0;

     FOR i IN 1..v_tab.count LOOP
        IF v_tab(i).OWNER != v_source_schema THEN
          CONTINUE;
        END IF;

        j := j+1;
        v_tname := v_tab(i).TABLE_NAME;
        v_rowcount1 := v_tab(i).NUM_ROWS;
        DBMS_OUTPUT.PUT(LPAD(j,3,'0')||' '||RPAD(v_tname,20)||TO_CHAR(v_rowcount1,'9,999,999,999'));
        v_sql := 'SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER='''||v_target_schema||''' AND TABLE_NAME='''||v_tname||'''';
        EXECUTE IMMEDIATE v_sql INTO v_found;

        IF v_found != 0 THEN
          v_sql := 'SELECT NVL(NUM_ROWS,0) FROM DBA_TABLES WHERE OWNER='''||v_target_schema||''' AND TABLE_NAME='''||v_tname||'''';
          EXECUTE IMMEDIATE v_sql INTO v_rowcount2;

          IF v_rowcount1 != v_rowcount2 THEN
            v_sql := 'SELECT COUNT(*) FROM '||v_source_schema||'.'||v_tname||'@SOURCE_DB_LINK';
            EXECUTE IMMEDIATE v_sql INTO v_rowcount1;
            v_sql := 'SELECT COUNT(*) FROM '||v_target_schema||'.'||v_tname;
            EXECUTE IMMEDIATE v_sql INTO v_rowcount2;
            IF v_rowcount1 != v_rowcount2 THEN
              v_match := 'FALSE';
            END IF;
          ELSE
            v_match := 'TRUE';
          END IF;
          DBMS_OUTPUT.PUT(TO_CHAR(v_rowcount2,'9,999,999,999'));

        ELSE
          DBMS_OUTPUT.PUT(LPAD('NOT FOUND',14));
          v_match := 'FALSE';
        END IF;

        IF v_match = 'TRUE' THEN
          DBMS_OUTPUT.PUT_LINE(LPAD('YES',10));
        ELSE
          DBMS_OUTPUT.PUT_LINE(LPAD('NO',10));
        END IF;
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
     DBMS_OUTPUT.PUT_LINE('Total No. of Tables Checked : '||j);
     SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') INTO v_time FROM DUAL;
     DBMS_OUTPUT.PUT_LINE('====================   End time : '||v_time||' ====================');
    END;
    /
    show errors;

  9. Create the comparing procedure in the target database.
  10. [oracle@targetserver ~]$ sqlplus system@target_db
    ...
    SQL> @compare_table_rowcount.sql

    Session altered.


    Procedure created.

    No errors.

  11. Execute the comparing procedure without remapping schema.
  12. When the schema name remains unchanged, you can execute the procedure given only one argument.
    SQL> SET SERVEROUTPUT ON;
    SQL> EXEC COMPARE_TABLE_ROWCOUNT(p_source_schema => 'OE');
    ==================== Begin time : 20121126 18:33:03 ====================
    Source Schema : OE
    Target Schema : OE
    ------------------------------------------------------------------
    ### Table Name             SOURCE ROWS   TARGET ROWS  MATCHED?
    ------------------------------------------------------------------
    001 CUSTOMERS                      319           319       YES
    002 INVENTORIES                  1,112         1,112       YES
    003 ORDERS                         105           105       YES
    004 ORDER_ITEMS                    665           665       YES
    005 PRODUCT_DESCRIPTIONS         8,640         8,640       YES
    006 PRODUCT_INFORMATION            288           288       YES
    007 PROMOTIONS                       2             2       YES
    008 WAREHOUSES                       9             9       YES
    ------------------------------------------------------------------
    Total No. of Tables Checked : 8
    ====================   End time : 20121126 18:33:03 ====================

    PL/SQL procedure successfully completed.

  13. Execute the comparing procedure with remapping schema.
  14. The procedure allows you to remap the source schema name to the target schema name for comparison. Therefore, you must specify two different arguments in the procedure.
    SQL> EXEC COMPARE_TABLE_ROWCOUNT(p_source_schema => 'SH', p_target_schema => 'HR');
    ==================== Begin time : 20121126 19:24:13 ====================
    Source Schema : SH
    Target Schema : HR
    ------------------------------------------------------------------
    ### Table Name             SOURCE ROWS   TARGET ROWS  MATCHED?
    ------------------------------------------------------------------
    001 CAL_MONTH_SALES_MV              48            48       YES
    002 CHANNELS                         5             5       YES
    003 COSTS                       82,112     NOT FOUND        NO
    004 COUNTRIES                       23            25        NO
    005 CUSTOMERS                   55,500     NOT FOUND        NO
    006 DIMENSION_EXCEPTIONS             0     NOT FOUND        NO
    007 DR$SUP_TEXT_IDX$I                0     NOT FOUND        NO
    008 DR$SUP_TEXT_IDX$K                0     NOT FOUND        NO
    009 DR$SUP_TEXT_IDX$N                0     NOT FOUND        NO
    010 DR$SUP_TEXT_IDX$R                0     NOT FOUND        NO
    011 FWEEK_PSCAT_SALES_MV        11,266     NOT FOUND        NO
    012 PRODUCTS                        72            72       YES
    013 PROMOTIONS                     503           503       YES
    014 SALES                      918,843     NOT FOUND        NO
    015 SALES_TRANSACTIONS_E             0     NOT FOUND        NO
    016 SUPPLEMENTARY_DEMOGR         4,500     NOT FOUND        NO
    017 TIMES                        1,826     NOT FOUND        NO
    ------------------------------------------------------------------
    Total No. of Tables Checked : 17
    ====================   End time : 20121126 19:24:13 ====================

    PL/SQL procedure successfully completed.

    If any target table cannot be found, it will be noted as "NOT FOUND". If the numbers of rows for any table do not match, it will show "NO" in the "MATCHED?" column, otherwise, it will show "YES".

How to Compare Two Databases After Migration (3/3) - Comparing Row Counts by Actually Counting

Leave a Reply

Your email address will not be published.