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.
- 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..
[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;
[oracle@targetserver ~]$ sqlplus system/password
...
SQL> @compare_table_rowcount_actual.sql
Session altered.
Procedure created.
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.
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.