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.
- Gather schemas statistics manually in the target database.
- Create a database link to the source database in the target database.
- Grant a system privilege to system in the target database.
- Edit the stored procedure in the target server.
- Create the comparing procedure in the target database.
- Execute the comparing procedure without remapping schema.
- Execute the comparing procedure with remapping schema.
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.
SQL> CREATE DATABASE LINK SOURCE_DB_LINK
2 CONNECT TO system IDENTIFIED BY system_password
3 USING 'source_db';
Database link created.
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.
[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;
[oracle@targetserver ~]$ sqlplus system@target_db
...
SQL> @compare_table_rowcount.sql
Session altered.
Procedure created.
No errors.
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.
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".