Skip to content

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

  • by
How to Compare Two Databases After Migration (2/3) - Comparing Row Counts by Leveraging Statistics
We have introduced a sample stored procedure to compare tables from source and target databases by leveraging table statistics
How to Compare Row Counts by Leveraging Statistics

But the values of column NUM_ROWS in DBA_TABLES may not be real-time, the result could be misleading. Here we modify the original store procedure into another one to count the tables from both databases actually, the result will be accurate, but It usually takes more time to complete.
  1. Edit the stored procedure in the target server.
  2. [oracle@targetserver ~]$ vi compare_table_rowcount_actual.sql
    SET SERVEROUTPUT ON;
    ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

    CREATE OR REPLACE PROCEDURE SYSTEM.COMPARE_TABLE_ROWCOUNT_ACTUAL(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));
     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 NVL(OWNER,'null_owner'), NVL(TABLE_NAME,'null_table') 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;
        DBMS_OUTPUT.PUT(LPAD(j,3,'0')||' '||RPAD(v_tname,20));
        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 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';
            ELSE
              v_match := 'TRUE';
            END IF;
              DBMS_OUTPUT.PUT(TO_CHAR(v_rowcount1,'9,999,999,999'));
              DBMS_OUTPUT.PUT(TO_CHAR(v_rowcount2,'9,999,999,999'));

        ELSE
          DBMS_OUTPUT.PUT(LPAD('.',14));
          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;

  3. Create the comparing procedure in the target database.
  4. [oracle@targetserver ~]$ sqlplus system/password
    ...
    SQL> @compare_table_rowcount_actual.sql

    Session altered.


    Procedure created.

  5. Execute the comparing procedure without remapping schema.
  6. SET SERVEROUTPUT ON;
    SQL> exec compare_table_rowcount_actual('OE');
    ==================== Begin time : 20121203 19:59:29 ====================
    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 : 20121203 19:59:29 ====================

    PL/SQL procedure successfully completed.

  7. Execute the comparing procedure with remapping schema..
  8. SQL> exec compare_table_rowcount_actual('SH','HR');
    ==================== Begin time : 20121203 19:58:40 ====================
    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                            .     NOT FOUND        NO
    004 COUNTRIES                       23            25        NO
    005 CUSTOMERS                        .     NOT FOUND        NO
    006 DIMENSION_EXCEPTIONS             .     NOT FOUND        NO
    007 DR$SUP_TEXT_IDX$I                .     NOT FOUND        NO
    008 DR$SUP_TEXT_IDX$K                .     NOT FOUND        NO
    009 DR$SUP_TEXT_IDX$N                .     NOT FOUND        NO
    010 DR$SUP_TEXT_IDX$R                .     NOT FOUND        NO
    011 FWEEK_PSCAT_SALES_MV             .     NOT FOUND        NO
    012 PRODUCTS                        72            72       YES
    013 PROMOTIONS                     503           503       YES
    014 SALES                            .     NOT FOUND        NO
    015 SALES_TRANSACTIONS_E             .     NOT FOUND        NO
    016 SUPPLEMENTARY_DEMOGR             .     NOT FOUND        NO
    017 TIMES                            .     NOT FOUND        NO
    ------------------------------------------------------------------
    Total No. of Tables Checked : 17
    ====================   End time : 20121203 19:58:40 ====================

    PL/SQL procedure successfully completed.

Go Back to Overview -How to Compare Two Databases After Migration (0/3) - An Overview
Tags:

Leave a Reply

Your email address will not be published.