Compare data of two oracle schemas.

gistfile1.sql

/****************************************************************************** 
* Compare data of two schemas.  
* Otput data is: 
* Table name of base schema: count of difference rows between two schemas  
******************************************************************************/ 
set serveroutput ON; 
DECLARE 
    s1 VARCHAR2(100) := 'Base_Shema'; -- base schema 
    s2 VARCHAR2(100) := 'Schema2'; 
BEGIN 
    DECLARE 
        total_val NUMBER; 
        CURSOR c IS 
          SELECT table_name 
          FROM   all_tables 
          WHERE  owner = s1; 
    BEGIN 
        FOR s IN c LOOP 
            DECLARE 
                r1       NUMBER; 
                r2       NUMBER; 
                sql_stmt VARCHAR2(500); 
            BEGIN 
                sql_stmt := 'select count(*) from ( select * from ' 
                            || s2 
                            || '.' 
                            || s.table_name 
                            ||' minus select * from ' 
                            || s1 
                            || '.' 
                            || s.table_name 
                            ||'      )'; 

                EXECUTE IMMEDIATE sql_stmt INTO r1; 

                sql_stmt := 'select count(*) from ( select * from ' 
                            || s1 
                            || '.' 
                            || s.table_name 
                            ||' minus select * from ' 
                            || s2 
                            || '.' 
                            || s.table_name 
                            ||'      )'; 

                EXECUTE IMMEDIATE sql_stmt INTO r2; 

                dbms_output.Put_line(s.table_name 
                                     || ': ' 
                                     || To_char(r1 + r2)); 
            EXCEPTION 
                WHEN OTHERS THEN 
                  dbms_output.Put_line(s.table_name 
                                       || ': ' 
                                       || SQLERRM); 
            END; 
        END LOOP; 
    END; 
END; 
/