×

Welcome to TagMyCode

Please login or create account to add a snippet.
1
0
 
1
Language: SQL
Posted by: Hariharan Radhakrishnan
Added: Nov 2, 2011 12:15 PM
Modified: Nov 2, 2011 12:21 PM
Views: 87
  1. REM
  2. REM compare_data.sql
  3. REM ================
  4. REM
  5. REM This script will compare the DATA IN TABLES OF the LOCAL Oracle schema
  6. REM TO identically named TABLES IN a remote Oracle schema AND generate a
  7. REM report OF DATA discrepencies. FOR each TABLE NOT containing identical
  8. REM ROWS IN the two schemas, the report will include a SQL query you can
  9. REM run TO display ALL OF the differing DATA.
  10. REM
  11. REM You can easily customize this script TO ONLY compare DATA IN certain
  12. REM TABLES, AND also TO exclude certain COLUMNS WITHIN each TABLE FROM the
  13. REM comparison.
  14. REM
  15. REM Please note that the ONLY way TO compare DATA IN TABLES IS TO READ ALL
  16. REM OF the DATA FROM each TABLE. Although this script IS pretty efficient
  17. REM IN the way it does the comparison, comparing the contents OF LARGE TABLES
  18. REM can be resource intensive AND can impact response TIME FOR other users.
  19. REM IF you will be USING this script TO compare LARGE volumes OF DATA, please
  20. REM take this INTO consideration.
  21. REM
  22. REM Oracle 8 AND Oracle 8i do NOT support fetching LOBs FROM remote TABLES,
  23. REM so DATA IN LOB COLUMNS are NOT compared BY this script. FOR SIMILAR
  24. REM reasons, LONGs AND LONG RAWs are NOT compared, either. TABLES containing
  25. REM LOB OR LONG COLUMNS will be compared, but DATA IN the LOB AND LONG COLUMNS
  26. REM will be LEFT OUT OF the comparison.
  27. REM
  28. REM Version 02-06-2002
  29. REM
  30.  
  31. REM
  32. REM Edit the following three DEFINE statements TO customize this script
  33. REM TO suit your needs.
  34. REM
  35.  
  36. REM TABLES TO be compared:
  37.  
  38. DEFINE table_criteria = "table_name = table_name" -- all tables
  39. REM DEFINE table_criteria = "table_name != 'TEST'"
  40. REM DEFINE table_criteria = "table_name LIKE 'LOOKUP%' OR table_name LIKE 'C%'"
  41.  
  42. REM COLUMNS TO be compared:
  43.  
  44. DEFINE column_criteria = "column_name = column_name" -- all columns
  45. REM DEFINE column_criteria = "column_name NOT IN ('CREATED', 'MODIFIED')"
  46. REM DEFINE column_criteria = "column_name NOT LIKE '%_ID'"
  47.  
  48. REM DATABASE link TO be used TO access the remote schema:
  49.  
  50. DEFINE dblink = "remote_db"
  51.  
  52. SET SERVEROUTPUT ON SIZE 1000000
  53. SET VERIFY OFF
  54.  
  55. DECLARE
  56.   CURSOR c_tables IS
  57.     SELECT   TABLE_NAME
  58.     FROM     user_tables
  59.     WHERE    &table_criteria
  60.     ORDER BY TABLE_NAME;
  61.   CURSOR c_columns (cp_table_name IN VARCHAR2) IS
  62.     SELECT   column_name, data_type
  63.     FROM     user_tab_columns
  64.     WHERE    TABLE_NAME = cp_table_name
  65.     AND      &column_criteria
  66.     ORDER BY column_id;
  67.   TYPE t_char80array IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
  68.   v_column_list     VARCHAR2(32767);
  69.   v_total_columns   INTEGER;
  70.   v_skipped_columns INTEGER;
  71.   v_count1          INTEGER;
  72.   v_count2          INTEGER;
  73.   v_rows_fetched    INTEGER;
  74.   v_column_pieces   t_char80array;
  75.   v_piece_count     INTEGER;
  76.   v_pos             INTEGER;
  77.   v_length          INTEGER;
  78.   v_next_break      INTEGER;
  79.   v_same_count      INTEGER := 0;
  80.   v_diff_count      INTEGER := 0;
  81.   v_error_count     INTEGER := 0;
  82.   v_warning_count   INTEGER := 0;
  83.   --
  84.   -- Use dbms_sql instead of native dynamic SQL so that Oracle 7 and Oracle 8
  85.   -- folks can use this script.
  86.   --
  87.   v_cursor          INTEGER := dbms_sql.open_cursor;
  88.   --
  89. BEGIN
  90.   --
  91.   -- Iterate through all tables in the local database that match the
  92.   -- specified table criteria.
  93.   --
  94.   FOR r1 IN c_tables LOOP
  95.     --
  96.     -- Build a list of columns that we will compare (those columns
  97.     -- that match the specified column criteria). We will skip columns
  98.     -- that are of a data type not supported (LOBs and LONGs).
  99.     --
  100.     v_column_list := NULL;
  101.     v_total_columns := 0;
  102.     v_skipped_columns := 0;
  103.     FOR r2 IN c_columns (r1.table_name) LOOP
  104.       v_total_columns := v_total_columns + 1;
  105.       IF r2.data_type IN ('BLOB', 'CLOB', 'NCLOB', 'LONG', 'LONG RAW') THEN
  106.         --
  107.         -- The column's data type is one not supported by this script (a LOB
  108.         -- or a LONG). We'll enclose the column name in comment delimiters in
  109.         -- the column list so that the column is not used in the query.
  110.         --
  111.         v_skipped_columns := v_skipped_columns + 1;
  112.         IF v_column_list LIKE '%,' THEN
  113.           v_column_list := RTRIM (v_column_list, ',') ||
  114.                            ' /*, "' || r2.column_name || '" */,';
  115.         ELSE
  116.           v_column_list := v_column_list || ' /* "' || r2.column_name ||'" */ ';
  117.         END IF;
  118.       ELSE
  119.         --
  120.         -- The column's data type is supported by this script. Add the column
  121.         -- name to the column list for use in the data comparison query.
  122.         --
  123.         v_column_list := v_column_list || '"' || r2.column_name || '",';
  124.       END IF;
  125.     END LOOP;
  126.     --
  127.     -- Compare the data in this table only if it contains at least one column
  128.     -- whose data type is supported by this script.
  129.     --
  130.     IF v_total_columns > v_skipped_columns THEN
  131.       --
  132.       -- Trim off the last comma from the column list.
  133.       --
  134.       v_column_list := RTRIM (v_column_list, ',');
  135.       BEGIN
  136.         --
  137.         -- Get a count of rows in the local table missing from the remote table.
  138.         --
  139.         dbms_sql.parse
  140.         (
  141.         v_cursor,
  142.         'SELECT COUNT(*) FROM (' ||
  143.         'SELECT ' || v_column_list || ' FROM "' || r1.table_name || '"' ||
  144.         ' MINUS ' ||
  145.         'SELECT ' || v_column_list || ' FROM "' || r1.table_name ||'"@&dblink)',
  146.         dbms_sql.native
  147.         );
  148.         dbms_sql.define_column (v_cursor, 1, v_count1);
  149.         v_rows_fetched := dbms_sql.execute_and_fetch (v_cursor);
  150.         IF v_rows_fetched = 0 THEN
  151.           RAISE NO_DATA_FOUND;
  152.         END IF;
  153.         dbms_sql.column_value (v_cursor, 1, v_count1);
  154.         --
  155.         -- Get a count of rows in the remote table missing from the local table.
  156.         --
  157.         dbms_sql.parse
  158.         (
  159.         v_cursor,
  160.         'SELECT COUNT(*) FROM (' ||
  161.         'SELECT ' || v_column_list || ' FROM "' || r1.table_name ||'"@&dblink'||
  162.         ' MINUS ' ||
  163.         'SELECT ' || v_column_list || ' FROM "' || r1.table_name || '")',
  164.         dbms_sql.native
  165.         );
  166.         dbms_sql.define_column (v_cursor, 1, v_count2);
  167.         v_rows_fetched := dbms_sql.execute_and_fetch (v_cursor);
  168.         IF v_rows_fetched = 0 THEN
  169.           RAISE NO_DATA_FOUND;
  170.         END IF;
  171.         dbms_sql.column_value (v_cursor, 1, v_count2);
  172.         --
  173.         -- Display our findings.
  174.         --
  175.         IF v_count1 = 0 AND v_count2 = 0 THEN
  176.           --
  177.           -- No data discrepencies were found. Report the good news.
  178.           --
  179.           dbms_output.put_line
  180.           (
  181.           r1.table_name || ' - Local and remote table contain the same data'
  182.           );
  183.           v_same_count := v_same_count + 1;
  184.           IF v_skipped_columns = 1 THEN
  185.             dbms_output.put_line
  186.             (
  187.             r1.table_name || ' - Warning: 1 LOB or LONG column was omitted ' ||
  188.             'from the comparison'
  189.             );
  190.             v_warning_count := v_warning_count + 1;
  191.           ELSIF v_skipped_columns > 1 THEN
  192.             dbms_output.put_line
  193.             (
  194.             r1.table_name || ' - Warning: ' || TO_CHAR (v_skipped_columns) ||
  195.             ' LOB or LONG columns were omitted from the comparison'
  196.             );
  197.             v_warning_count := v_warning_count + 1;
  198.           END IF;
  199.         ELSE
  200.           --
  201.           -- There is a discrepency between the data in the local table and
  202.           -- the remote table. First, give a count of rows missing from each.
  203.           --
  204.           IF v_count1 > 0 THEN
  205.             dbms_output.put_line
  206.             (
  207.             r1.table_name || ' - ' ||
  208.             LTRIM (TO_CHAR (v_count1, '999,999,990')) ||
  209.             ' rows on local database missing from remote'
  210.             );
  211.           END IF;
  212.           IF v_count2 > 0 THEN
  213.             dbms_output.put_line
  214.             (
  215.             r1.table_name || ' - ' ||
  216.             LTRIM (TO_CHAR (v_count2, '999,999,990')) ||
  217.             ' rows on remote database missing from local'
  218.             );
  219.           END IF;
  220.           IF v_skipped_columns = 1 THEN
  221.             dbms_output.put_line
  222.             (
  223.             r1.table_name || ' - Warning: 1 LOB or LONG column was omitted ' ||
  224.             'from the comparison'
  225.             );
  226.             v_warning_count := v_warning_count + 1;
  227.           ELSIF v_skipped_columns > 1 THEN
  228.             dbms_output.put_line
  229.             (
  230.             r1.table_name || ' - Warning: ' || TO_CHAR (v_skipped_columns) ||
  231.             ' LOB or LONG columns were omitted from the comparison'
  232.             );
  233.             v_warning_count := v_warning_count + 1;
  234.           END IF;
  235.           --
  236.           -- Next give the user a query they could run to see all of the
  237.           -- differing data between the two tables. To prepare the query,
  238.           -- first we'll break the list of columns in the table into smaller
  239.           -- chunks, each short enough to fit on one line of a telnet window
  240.           -- without wrapping.
  241.           --
  242.           v_pos := 1;
  243.           v_piece_count := 0;
  244.           v_length := LENGTH (v_column_list);
  245.           LOOP
  246.             EXIT WHEN v_pos = v_length;
  247.             v_piece_count := v_piece_count + 1;
  248.             IF v_length - v_pos < 72 THEN
  249.               v_column_pieces(v_piece_count) := SUBSTR (v_column_list, v_pos);
  250.               v_pos := v_length;
  251.             ELSE
  252.               v_next_break :=
  253.                 GREATEST (INSTR (SUBSTR (v_column_list, 1, v_pos + 72),
  254.                                  ',"', -1),
  255.                           INSTR (SUBSTR (v_column_list, 1, v_pos + 72),
  256.                                  ',/* "', -1),
  257.                           INSTR (SUBSTR (v_column_list, 1, v_pos + 72),
  258.                                  ' /* "', -1));
  259.               v_column_pieces(v_piece_count) :=
  260.                 SUBSTR (v_column_list, v_pos, v_next_break - v_pos + 1);
  261.               v_pos := v_next_break + 1;
  262.             END IF;
  263.           END LOOP;
  264.           dbms_output.put_line ('Use the following query to view the data ' ||
  265.                                 'discrepencies:');
  266.           dbms_output.put_line ('(');
  267.           dbms_output.put_line ('SELECT ''Local'' "LOCATION",');
  268.           FOR i IN 1..v_piece_count LOOP
  269.             dbms_output.put_line (v_column_pieces(i));
  270.           END LOOP;
  271.           dbms_output.put_line ('FROM "' || r1.table_name || '"');
  272.           dbms_output.put_line ('MINUS');
  273.           dbms_output.put_line ('SELECT ''Local'' "LOCATION",');
  274.           FOR i IN 1..v_piece_count LOOP
  275.             dbms_output.put_line (v_column_pieces(i));
  276.           END LOOP;
  277.           dbms_output.put_line ('FROM "' || r1.table_name || '"@&dblink');
  278.           dbms_output.put_line (') UNION ALL (');
  279.           dbms_output.put_line ('SELECT ''Remote'' "LOCATION",');
  280.           FOR i IN 1..v_piece_count LOOP
  281.             dbms_output.put_line (v_column_pieces(i));
  282.           END LOOP;
  283.           dbms_output.put_line ('FROM "' || r1.table_name || '"@&dblink');
  284.           dbms_output.put_line ('MINUS');
  285.           dbms_output.put_line ('SELECT ''Remote'' "LOCATION",');
  286.           FOR i IN 1..v_piece_count LOOP
  287.             dbms_output.put_line (v_column_pieces(i));
  288.           END LOOP;
  289.           dbms_output.put_line ('FROM "' || r1.table_name || '"');
  290.           dbms_output.put_line (');');
  291.           v_diff_count := v_diff_count + 1;
  292.         END IF;
  293.       EXCEPTION
  294.         WHEN OTHERS THEN
  295.           --
  296.           -- An error occurred while processing this table. (Most likely it
  297.           -- doesn't exist or has fewer columns on the remote database.)
  298.           -- Show the error we encountered on the report.
  299.           --
  300.           dbms_output.put_line (r1.table_name || ' - ' || SQLERRM);
  301.           v_error_count := v_error_count + 1;
  302.       END;
  303.     END IF;
  304.   END LOOP;
  305.   --
  306.   -- Print summary information.
  307.   --
  308.   dbms_output.put_line ('-------------------------------------------------');
  309.   dbms_output.put_line
  310.   (
  311.   'Tables examined: ' || TO_CHAR (v_same_count + v_diff_count + v_error_count)
  312.   );
  313.   dbms_output.put_line
  314.   (
  315.   'Tables with data discrepencies: ' || TO_CHAR (v_diff_count)
  316.   );
  317.   IF v_warning_count > 0 THEN
  318.     dbms_output.put_line
  319.     (
  320.     'Tables with warnings: ' || TO_CHAR(v_warning_count)
  321.     );
  322.   END IF;
  323.   IF v_error_count > 0 THEN
  324.     dbms_output.put_line
  325.     (
  326.     'Tables that could not be checked due to errors: ' || TO_CHAR(v_error_count)
  327.     );
  328.   END IF;
  329.   dbms_sql.close_cursor (v_cursor);
  330. END;
  331. /