Oracle data comparison (DBMS_COMPARISON)

Oracle data comparison (DBMS_COMPARISON)

A data comparison package (DBMS_COMPARISON), 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 NO
SYS CREATE DATABASE LINK NO
OWB $ CLIENT CREATE DATABASE LINK NO
IMP_FULL_DATABASE CREATE PUBLIC DATABASE LINK NO
RECOVERY_CATALOG_OWNER CREATE DATABASE LINK NO
DBA DROP PUBLIC DATABASE LINK YES
IMP_FULL_DATABASE DROP PUBLIC DATABASE LINK NO
OWBSYS CREATE 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 'issue 1 ';

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> create database link comparison_link connect to scott identified by root using 'issue 1 ';

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>

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

For more details, please continue to read the highlights on the next page:

1 2 Next Page