Oracle data comparison (DBMS_COMPARISON)

Oracle data comparison (DBMS_COMPARISON)

Today is. I feel ashamed to have received an invitation from csdn to apply for a blog expert. I have never been able to write anything since I changed my job. Today, one of my colleagues posted a data comparison package (DBMS_COMPARISON) in the group, but this package is less than used. So let's talk about the use of this Toolkit today.

For friends who often finish data migration, after data migration, the most important and important question is whether the data on the target end is consistent with that on the source end. Whether data consistency is related to the success or failure of data migration in large oracle databases. Many companies have begun to develop their own comparison tools, such as rowid-based comparison of dsg and minus-based comparison. However, the database also provides an interface for data comparison, that is, the DBMS_COMPARISON software package.

DBMS_COMPARISION introduction:

This software package is provided by oracle to compare objects between two other data sets. In addition, if the source and target data are inconsistent during the comparison, you can choose whether to copy data from the source to the target or from the target to the source, the final result is data consistency. This package is also implemented by creating dblink. This tool can be used in four steps:

Step 1: Use create_compare to create a comparison action

Step 2: Use the compare function to compare data objects

Step 3: Check the comparison result. The corresponding record is recorded in different views as follows:

DBA_COMPARISON_SCAN

USER_COMPARISON_SCAN

DBA_COMPARISON_SCAN_VALUES

USER_COMPARISON_SCAN_VALUES

DBA_COMPARISON_ROW_DIF

USER_COMPARISON_ROW_DIF

Fourth, if the data is inconsistent, you can use convert to synchronize the data.

It may be said that if I compare the data twice, how can we differentiate it? This is what oracle will design for you. This function is recheck. Follow-up introduction:

Another question is, what data can be compared with this package?

The answer is: tables, views, materialized views, synonyms, etc.

DBMS_COMPARISION restrictions:

Of course, any tool has its own restrictions. What about this package?

1. The source database version must be higher than 11.1, and the target database version must be higher than 10.1.

2. All database objects for comparison must be shared objects, that is, the number of columns of each object must be consistent with the column type. If the columns are inconsistent, you must use column_list to list the columns to be compared.

Database objects of different types can be compared and converged at different databases. For example, a table at one database and a materialized view at another database can be compared and converged with this package.

The above describes the restrictions that are easy to understand. The following describes the restrictions on index columns:

1. In full-database comparison mode, a single index column of the number, timestamp, interval, or DATE data type must exist, or there is only one composite index that includes these data types, but the columns designed in this composite index must be not null or one of the columns must be a primary key column.

2,

For the scan modes CMP_SCAN_MODE_FULL and CMP_SCAN_MODE_CUSTOM to be supported, the database objects must have one of the following types of indexes:

A single-column index on a number, timestamp, interval, DATE, VARCHAR2, or CHAR data type column

A composite index that only includes des number, timestamp, interval, DATE, VARCHAR2, or CHAR columns. each column in the composite index must either have a not null constraint or be part of the primary key.

If the database does not meet these requirements, this package will not be able to compare data.

If the database objects have only one index, and it is a composite index that contains des a NUMBER column and an NCHAR column, then the DBMS_COMPARISON package does not support them.

If these constraints are not present on a table, then use the index_schema_name and index_name parameters in the CREATE_COMPARISON procedure to specify an index whose columns satisfy this requirement.

When a single index value identifies both a local row and a remote row, the two rows must be copies of the same row in the replicated tables. in addition, each pair of copies of the same row must always have the same index value.

Data Types not supported by DBms_comparison:

LONG, lang raw, ROWID, urowid, clob, nclob, blob, and bfile are as follows:

1. udt (user-defined types, including object types, REFs, varrays, and nested tables)

2. oracle-supplied type (including any types, XML types, spatial types, and media types)

Now, after learning about this, we will start to do it in person, simply saying that we will not be able to practice it.

First, create dblink:

SQL> select * from dba_sys_privs rhys where rhys.privilege like upper('%link%');GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---SYS                            DROP PUBLIC DATABASE LINK                NOSYS                            CREATE DATABASE LINK                     NOOWB$CLIENT                     CREATE DATABASE LINK                     NOIMP_FULL_DATABASE              CREATE PUBLIC DATABASE LINK              NORECOVERY_CATALOG_OWNER         CREATE DATABASE LINK                     NODBA                            DROP PUBLIC DATABASE LINK                YESIMP_FULL_DATABASE              DROP PUBLIC DATABASE LINK                NOOWBSYS                         CREATE DATABASE LINK                     YESIMP_FULL_DATABASE              CREATE DATABASE LINK                     NOSYS                            CREATE PUBLIC DATABASE LINK              NODBA                            CREATE PUBLIC DATABASE LINK              YESGRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---DBA                            CREATE DATABASE LINK                     YES12 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 userUSER 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                    2012 rows selected.SQL> 

Step 2: Create a comparison task:

If you forget to mention the permission, you must have the following permissions for the package:

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> 

So that we can finish the first step.

When the columns of the data objects on the source and target are inconsistent, the following error occurs:

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 5008ORA-06512: at "SYS.DBMS_COMPARISON", line 448ORA-06512: at line 2

So what should we do? I am talking about creating a column_list;

Step 2 start data comparison:

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> 

Step 3 view the comparison result:

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-------------------- ---------- -------------- ------------ ---------------- ----------------- ----------------- ---------- - ----------------------------------------TEST1                         1                           1 SUC                              0                 0          4 N 19-AUG-14 11.05.42.780593 PMTEST1                         2                           2 SUC                              0                 0          4 N 19-AUG-14 11.11.37.613343 PMSQL> select * from user_comparison_row_dif;no rows selectedSQL>

Now, it's easy to do.

Of course, if you are willing to be interested, you can test others on your own.

I want to clear the data:

SQL> begin  2  dbms_comparison.purge_comparison(  3  comparison_name=>'test1');  4  end;  5  /PL/SQL procedure successfully completed.SQL> select * from user_comparison_scan;no rows selectedSQL> 

That's all!