oracle 两表两列数据对比_Oracle中比对2张表之间数据是否一致的几种方法

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

1. 所要比对的表的结构是一致的

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

方式1：

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

Source 源端版本为11gR2 :

conn maclean/maclean

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

www.oracledatabase12g.com & www.askmaclean.com

drop table test1;

create table test1 tablespace users as select object_id t1,object_name t2 from dba_objects where object_id is not null;

alter table test1 add primary key(t1);

exec dbms_stats.gather_table_stats('MACLEAN','TEST1',cascade=>TRUE);

create database link maclean connect to maclean identified by maclean using 'G10R21';

Database link created.

以上源端数据库版本为11.2.0.3 ， 源表结构为test1(t1 number primary key,t2 varchar2(128)，透过dblink链接到版本为10.2.0.1的目标端

conn maclean/maclean

SQL> select * from v$version

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

create table test2 tablespace users as select object_id t1,object_name t2

from dba_objects where object_id is not null;

alter table test2 add primary key(t1);

exec dbms_stats.gather_table_stats('MACLEAN','TEST2',cascade=>TRUE);

目标端版本为10.2.0.1 ， 表结构为test2(t1 number primary key,t2 varchar2(128))。

注意这里2张表上均必须有相同的主键索引或者伪主键索引(pseudoprimary key伪主键要求是唯一键且所有的成员列均是非空NOT NULL)。

实际创建comparison对象，并实施校验：

begin

dbms_comparison.create_comparison(comparison_name    => 'MACLEAN_TEST_COM',

schema_name        => 'MACLEAN',

object_name        => 'TEST1',

dblink_name        => 'MACLEAN',

remote_schema_name => 'MACLEAN',

remote_object_name => 'TEST2',

scan_mode          => dbms_comparison.CMP_SCAN_MODE_FULL);

end;

PL/SQL procedure successfully completed.

SQL> set linesize 80 pagesize 1400

SQL> select * from user_comparison where comparison_name='MACLEAN_TEST_COM';

COMPARISON_NAME COMPA SCHEMA_NAME

------------------------------ ----- ------------------------------

OBJECT_NAME OBJECT_TYPE REMOTE_SCHEMA_NAME

------------------------------ ----------------- ------------------------------

REMOTE_OBJECT_NAME REMOTE_OBJECT_TYP

------------------------------ -----------------

DBLINK_NAME

--------------------------------------------------------------------------------

SCAN_MODE SCAN_PERCENT

--------- ------------

CYCLIC_INDEX_VALUE

--------------------------------------------------------------------------------

NULL_VALUE

--------------------------------------------------------------------------------

LOCAL_CONVERGE_TAG

--------------------------------------------------------------------------------

REMOTE_CONVERGE_TAG

--------------------------------------------------------------------------------

MAX_NUM_BUCKETS MIN_ROWS_IN_BUCKET

--------------- ------------------

LAST_UPDATE_TIME

---------------------------------------------------------------------------

MACLEAN_TEST_COM TABLE MACLEAN

TEST1 TABLE MACLEAN

TEST2 TABLE

MACLEAN

FULL

ORA$STREAMS$NV

1000 10000

20-DEC-11 01.08.44.562092 PM

利用dbms_comparison.create_comparison创建comparison后，新建的comparison会出现在user_comparison视图中；

以上我们完成了comparison的创建，但实际的校验仍未发生我们利用10046事件监控这个数据对比过程：

conn maclean/maclean

set timing on;

alter system flush shared_pool;

alter session set events '10046 trace name context forever,level 8';

set serveroutput on

DECLARE

retval dbms_comparison.comparison_type;

BEGIN

IF dbms_comparison.compare('MACLEAN_TEST_COM', retval, perform_row_dif => TRUE) THEN

dbms_output.put_line('No Differences');

ELSE

dbms_output.put_line('Differences Found');

END IF;

END;

/

Differences Found =====> 返回结果为Differences Found，说明数据存在差异并不一致

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.87

===========================10046 tkprof result =========================

SELECT MIN("T1"), MAX("T1")

FROM

"MACLEAN"."TEST1"