您的当前位置：首页正文

谈oracle数据比对(DBMS_COMPARISON)

2023-11-11 来源：东饰资讯网

今天是2014-08-19，我今天收到csdn给我发的申请博客专家的邀请，自己感觉实在羞愧啊。

自从换了工作也一直没有精力在写点东西了。今天我一个同事，在群里贴出了一个数据比对的包（DBMS_COMPARISON)，可是这个包相比用的比較少。

所以今天就谈谈这个工具包的使用吧。

对于常常完数据迁移的朋友来说，在数据挪动之后，最重要也是最关键和最关心的一个问题是，目标端和源端的数据是否一致。数据的一致是否关系着大型oracle数据库数据迁移的成败与否。眼下非常多公司都開始研发自己的对照工具。如dsg的基于rowid的比对、基于minus的比对等等。可是数据库本身也是给我们提供了一个数据比对的接口，那就是这个DBMS_COMPARISON软件包。

DBMS_COMPARISION简单介绍：

这个软件包是oracle提供的能够再两个数据之间做object是比对。而且呢假设在比对过程中假设源端数据和目标端数据不一致。那么能够选择是从源端在将数据拷贝到目标端。还是从目标端在拷贝到源端。终于达到数据一致性的结果。该包也是通过创建dblink来实现的。这个工具的使用大体分为四步：

第一步：使用create_compare去创建一个比对动作

第二步：使用compare函数去进行数据对象之间的比对

第三步：我们在去查看比对结果，对应的record会记录到不同视图中例如以下：

DBA_COMPARISON_SCAN

USER_COMPARISON_SCAN

DBA_COMPARISON_SCAN_VALUES

USER_COMPARISON_SCAN_VALUES

DBA_COMPARISON_ROW_DIF

USER_COMPARISON_ROW_DIF

第四不：假设数据不一致，那么能够使用convert去将数据同步

大家可能会说，假设我进行了两次数据比对，那么怎样区分呢，这就是oracle自己会给你设计一个标示了。这个函数是recheck。

兴许介绍：

另一个问题。那就是这个包能做哪些数据比对？

答案是：对表、视图、物化视图、同义词等

DBMS_COMPARISION限制：

当然了不论什么一个工具都有自己的限制，那么这个包呢？

1、对于源端数据库版本号必须是高于11.1，对于目标端数据库版本号必须高于10.1

2.对于全部比对的数据库对象，必须是共享对象。也就是说每一个对象的列个数和列的类型必须一致。假设列不一致，那么须要将比对的列使用column_list做个列表。

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.

以上是说了比較easy理解的限制，以下在说一下索引列的限制：

1、在全库比对模式下，必需要有一个在 number, timestamp, interval, or DATE 数据类型的单一索引列，或是唯独一个包含这几种数据类型的复合索引，可是这个复合索引中设计到的列必须都是not null或是当中一列是一个主键列。

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 number, timestamp, interval, DATE,VARCHAR2, or CHAR columns. Each column in the composite index must either have aNOT NULL constraint or be part of the primary key.

假设数据库没有满足这些要求。那么这个包将无法进行数据比对。

if the database objects have only one index, and it is a composite index that includes aNUMBER 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 andindex_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.

DBms_comparison不支持的数据类型：

LONG、LANG RAW、ROWID、urowid、clob、nclob、blob、bfile另外还有例如以下两种：

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)

好了。了解这些后，我们就開始去亲自做一下，光说不练那不行。

第一创建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>

第二步创建比对任务：

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

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

好了，简简单单就到这了。

当然假设敢兴趣能够自己在測试其它的。

我要把数据清掉了：

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!

谈oracle数据比对(DBMS_COMPARISON)

标签：code res create grant ram 过程 type ora raw

小编还为您整理了以下内容，可能对您也有帮助：

oracle中如何比较两个表之间所有栏位的数据是否完全相同

Oracle中比对2张表之间数据是否一致的几种方法
大约是2个星期前做一个夜班的时候，开发人员需要比对shareplex 数据同步复制软件在源端和目标端的2张表上的数据是否一致，实际上后来想了下shareplex 本身应当具有这种数据校验功能，但是还是希望从数据库的角度得出几种可用的同表结构下的数据比对方法。

注意以下几种数据比对方式适用的前提条件：

1. 所要比对的表的结构是一致的2. 比对过程中源端和目标端表上的数据都是静态的，没有任何DML修改

方式1：
假设你所要进行数据比对的数据库其中有一个版本为11g且该表上有相应的主键索引(primary key index)或者唯一非空索引(unique key ¬ null)的话，那么恭喜你！　你可以借助11g 新引入的专门做数据对比的PL/SQL Package dbms_comparison来实现数据校验的目的，如以下演示：

askmaclean

oracle中如何比较两个表之间所有栏位的数据是否完全相同

oracle 怎么对比两条数据

方法一、用PL/SQL DEVELOPER 来比较
1.登陆数据库A.
2.打开TOOLS菜单下的Compare User Objects
3.点Target Session，登陆数据库B
4.执行Compare
5.返回的是所有不同的对象以及更新语句[@more@]方法二、
1、对需要比较的表进行分析（可全表可抽样，自选）,否则统计信息不准；
2、通过dba_tab_col_statistics表的column_name字段进行比较
select tz.table_name ,tz.column_name from dba_tab_col_statistics tz
WHERE tz.owner = 'BSS' AND TZ.TABLE_NAME LIKE 'BSS_%'
and (TZ.TABLE_NAME ,TZ.COLUMN_NAME) not in(
select TZT.TABLE_NAME ,TZT.COLUMN_NAME
from dba_tab_col_statistics@tz_bsstzt tzt
WHERE tzt.owner = 'BSS' AND TZT.TABLE_NAME LIKE 'BSS_%')

oracle 怎么对比两条数据

在oracle中怎样对比两个数据库中相同表的数据?

你是两个数据库还是一个库下的两个USER！~
如果是一个库里的两个user
SELECT * FROM SANWA_ADMIN.MU010,UCOOP.MC00030
在表前面加上用户名就可以了！~
你还是详细说下吧！~

在oracle中怎样对比两个数据库中相同表的数据?

oracle怎么核对两个表的数据是否一致

例如：核对web层和mid层保费收入的差异：
首先把这两个模块看做两张表，而且这两个模块出的字段一致，需要编号和钱，核对这个钱的数据为啥不对。
①WEB层
SELECT SUM(A.PREMIUMCNY) "保费收入(含税) "
FROM WEB_XG_SALESMAN_RISK_ALL A
WHERE TRUNC(A.STATDATE) BETWEEN DATE '2019-09-01' AND DATE '2019-09-30';
②MID层
SELECT SUM(NVL(A.PREMIUM, 0)) AS 保费收入
FROM MID_CB_PREMIUM_RISK_ALL A
WHERE TRUNC(A.STATDATE) BETWEEN DATE'2019-09-01' AND DATE'2019-09-30';
----------------------------------------------------------------------------------------
结果：（需要把两个模块的编号和钱都写出来）
SQL：
SELECT A.POLICYNO, --编号
SUM(A."保费收入(含税) ") AS BFSRHS,
B.POLICYNO,
SUM(B.保费收入) AS BFSR
FROM
(SELECT A.POLICYNO,SUM(A.PREMIUMCNY) "保费收入(含税) "
FROM WEB_XG_SALESMAN_RISK_ALL A
WHERE TRUNC(A.STATDATE) BETWEEN DATE '2019-09-01' AND DATE '2019-09-30'
GROUP BY A.POLICYNO
) A
FULL JOIN
(SELECT A.POLICYNO,SUM(NVL(A.PREMIUM, 0)) AS 保费收入
FROM MID_CB_PREMIUM_RISK_ALL A
WHERE TRUNC(A.STATDATE) BETWEEN DATE'2019-09-01' AND DATE'2019-09-30'
GROUP BY A.POLICYNO
) B
ON A.POLICYNO = B.POLICYNO --用编号关联
WHERE A.POLICYNO IS NULL OR B.POLICYNO IS NULL --是否一致
OR A.BFSRHS <> B.BFSR
group BY A.POLICYNO,
B.POLICYNO

oracle怎么核对两个表的数据是否一致

在oracle中怎样对比两个数据库中相同表的数据？

你是两个数据库还是一个库下的两个USER！~

如果是一个库里的两个user

SELECT * FROM SANWA_ADMIN.MU010,UCOOP.MC00030

在表前面加上用户名就可以了！~

你还是详细说下吧！~

我想比较两个oracle数据库表结构的差异,有现成的工具或脚本吗

有的事，很多编程工具中打开数据库表都可以比较的。
也可以在oracle的管理控制台中打开两个表比较，也很直观。

我想比较两个oracle数据库表结构的差异,有现成的工具或脚本吗

有的事，很多编程工具中打开数据库表都可以比较的。
也可以在oracle的管理控制台中打开两个表比较，也很直观。

oracle 查看表结构，表里的数据

1、首先，我们打开PLSQL工具连接到需要进行数据比对的ORACLE数据库。

2、登录成功后，点击工具（tool）选择匹配用户结构（compare user objects）我们先匹配数据表结构以防止匹配数据时造成数据无法修改的风险。

3、在弹出的界面中选择我们需要匹配的数据表，点击目标会话（target session）输入需要匹配数据的对应数据库用户名密码，点击ok连接成功后单击匹配数据（compare）。

4、如果数据表结构有差异在弹出的界面会显示数据库中表结构的差异，并形成相关的升级sql语句，数据表匹配只考虑源数据库中没有的表或列，查看sql语句是否为我们想要匹配的，如果是点击确认匹配，数据库表结构匹配完成。

5、登录成功后，点击工具（tool）选择匹配表数据（compare table data）。

6、如果是点击确认匹配，数据库表数据匹配完成。

如何用PL/SQL对oracle两个表的数据做比对,建立oracle视图结果集

例子：merge

into

test1

using

test2

(t1.id

t2.id)

when

matched

then

update

set

t1.name

t2.name

when

not

matched

then

insert

values(t2.id,t2.name)

--这个相当于条件判断，这个例子的含义包括了update和insert，所以可以通俗理解为和文件粘贴复制后直接替换一样。