谈oracle数据比对(DBMS_COMPARISON) - 数据库编程

REATE DATABASE LINK YES IMP_FULL_DATABASE CREATE DATABASE LINK NO SYS CREATE PUBLIC DATABASE LINK NO DBA CREATE PUBLIC DATABASE LINK YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA CREATE DATABASE LINK YES 12 rows selected. SQL> grant create database link to scott; Grant succeeded.

SQL> create database link comparison_link connect to scott identified by root using 'orac1';

Database link created.

SQL> show user
USER is "SCOTT"
SQL> select * from scott.emp@comparison_link;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DEP
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------------------------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DEP
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------------------------
      7902 AMY        ANALYST         7566 03-DEC-81       3000                    20

12 rows selected.

SQL>

第二步创建比对任务：

对了忘记提一下权限了，对于该包，要有如下权限：

SQL> grant execute on dbms_comparison to scott;

Grant succeeded.

SQL> grant execute_catalog_role to scott;

Grant succeeded.

SQL>

SQL>  begin
  2   dbms_comparison.create_comparison(
  3   comparison_name=>'test1',
  4   schema_name=>'SCOTT',
  5   object_name=>'DEPT',
  6   dblink_name=>'comparison_link'
  7   );
  8   end;
  9  /

PL/SQL procedure successfully completed.

SQL>

好这样就做完第一步了。

当源端和目标端数据对象的列不一致的情况会出现如下错误：

SQL>  begin
  2   dbms_comparison.create_comparison(
  3   comparison_name=>'test1',
  4   schema_name=>'SCOTT',
  5   object_name=>'EMP',
  6   dblink_name=>'comparison_link'
  7   );
  8   end;
  9  /
 begin
*
ERROR at line 1:
ORA-23625: Table shapes of SCOTT.EMP and SCOTT.EMP@COMPARISON_LINK did not match.
ORA-06512: at "SYS.DBMS_COMPARISON", line 5008
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 2

那么怎么办呢？和我说的是做一个column_list;

第二步开始进行数据比对：

SQL> declare
  2   compare_info dbms_comparison.comparison_type;
  3   compare_return boolean;
  4   begin
  5   compare_return := dbms_comparison.compare (comparison_name=>'test1',
  6   scan_info=>compare_info,
  7   perform_row_dif=>TRUE);
  8   
  9  if compare_return=TRUE
 10   then
 11   dbms_output.put_line('the tables are equivalent.');
 12   else
 13   dbms_output.put_line('Bad news... there is data divergence.');
 14   dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'||compare_info.scan_id);
 15   end if;
 16   end;
 17   /
the tables are equivalent.

PL/SQL procedure successfully completed.

SQL>

第三步查看比对结果：

SQL> select * from user_comparison_scan
  2  ;

COMPARISON_NAME         SCAN_ID PARENT_SCAN_ID ROOT_SCAN_ID STATUS           CURRENT_DIF_COUNT INITIAL_DIF_COUNT COUNT_ROWS S LAST_UPDATE_TIME
-------------------- ---------- -------------- ------------ ---------------- -----

谈oracle数据比对(DBMS_COMPARISON)(二)