oracle 表字段对比,Oracle dblink比较两个库中的表字段

Oracle dblink比较两个库中的表字段

1

A库IP为10.0.30.65，olduser用户下有个表oldtable，该表只有一个列oldid。

B库IP为10.0.30.64，newuser用户下有个表newtable，该表只有一个列newid。

在A库中执行

create user olduser identified by old123;

grant connect, resource to olduser;

connect olduser/old123;

create table oldtable(oldid number);

insert into oldtable values(1);

insert into oldtable values(2);

insert into oldtable values(3);

commit;

在B库中执行

create user newuser identified by new123;

grant connect, resource to newuser;

connect newuser/new123;

create table newtable(newid number);

insert into newtable values(2);

insert into newtable values(3);

insert into newtable values(4);

commit;

2

B库的/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora中配置连接A库的网络服务名old

OLD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL =

TCP)(HOST = 10.0.30.65)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME =

orcl)

)

)

3

在B库中用system帐户创建远程连接A库的dblink

conn system/oracle

create public database link old_db connect to olduser identified

by old123 using 'old';

4

查找newtable中没有而oldtable中有的字段

conn newuser/new123;

select oldid from oldtable@old_db where oldid not in (select

newid from newtable);

OLDID

----------

1

5

查找oldtable中没有而newtable中有的字段

select newid from

newtable where newid not in (select oldid from oldtable@old_db);

NEWID

----------

4