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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29485627/viewspace-1839762/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29485627/viewspace-1839762/