Home » SQL & PL/SQL » SQL & PL/SQL » comparing data between two databases
comparing data between two databases [message #306366] Thu, 13 March 2008 22:54 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
I want to compare the tables between two schemas residing in two different database, can you some one tell how to do this?
Thanks
Re: comparing data between two databases [message #306367 is a reply to message #306366] Thu, 13 March 2008 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>can you some one tell how to do this?
By using a database link?

Are both GOOGLE & the search function on this forum broken for you?
Re: comparing data between two databases [message #306375 is a reply to message #306367] Thu, 13 March 2008 23:59 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Using DB_LINK
SQL> select owner, db_link,username,host
  2  from dba_db_links;

OWNER                          DB_LINK

USERNAME
------------------------------ -------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
HOST
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SYS                            META

SCOTT
META

SYSTEM                         TEST1

SCOTT
META
SQL> 
SQL> drop database link meta;

Database link dropped.

SQL> create database link meta connect to scott identified by tiger using 'META'
;

Database link created.

SQL>
SQL>  select EMP_TEST.empno TABLE_1_EMPNO,
  2   EMP_TEST.ename TABLE1_ENAME,
  3   EMP.empno TABLE2_EMPNO,
  4   EMP.ename TABLE2_ENAME
  5   from EMP_TEST EMP_TEST, EMP@meta EMP
  6   where EMP_TEST.ename=EMP.ename
  7   /

TABLE_1_EMPNO TABLE1_ENA TABLE2_EMPNO TABLE2_ENA
------------- ---------- ------------ ----------
         7369 SMITH              7369 SMITH
         7499 ALLEN              7499 ALLEN
         7521 WARD               7521 WARD
         7566 JONES              7566 JONES
         7654 MARTIN             7654 MARTIN
         7698 BLAKE              7698 BLAKE
         7782 CLARK              7782 CLARK
         7788 SCOTT              7788 SCOTT
         7839 KING               7839 KING
         7844 TURNER             7844 TURNER
         7876 ADAMS              7876 ADAMS
         7900 JAMES              7900 JAMES
         7902 FORD               7902 FORD
         7934 MILLER             7934 MILLER

14 rows selected.

SQL>
Re: comparing data between two databases [message #306555 is a reply to message #306367] Fri, 14 March 2008 12:18 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
hey
anacedent
I know we have to use DB like, but my question was how?
if you dont have the answer better keep quite

[Updated on: Fri, 14 March 2008 12:19]

Report message to a moderator

Re: comparing data between two databases [message #306566 is a reply to message #306555] Fri, 14 March 2008 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I know we have to use DB like, but my question was how?
if you dont have the answer better keep quite

But this is so very simple and perfectly shown in CREATE DATABASE LINK documentation that it is not worth posting another example.
Are you so lazy that you can't even read the documentation examples?

This said, you can at least thank trantuananh24hg to do your job instead of complain about those who don't give you the answer you want.

You have 2 behaviours:
- those who don't make a a little effort and mourn for an answer
- those who try to find by themselves and thank those who help
You choose your way. Is it the best one?

Regards
Michel

Re: comparing data between two databases [message #306580 is a reply to message #306555] Fri, 14 March 2008 14:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
anacedent wrote on Fri, 14 March 2008 04:56
>can you some one tell how to do this?
By using a database link?

Are both GOOGLE & the search function on this forum broken for you?

caprikar wrote on Fri, 14 March 2008 18:18
hey
anacedent
I know we have to use DB like, but my question was how?
if you dont have the answer better keep quite

Me thinks anacedent's post added more to this thread (hey, the guy even gave you your answer!) than yours, so telling ana to keep quiet sounds like the pot and the kettle to me..
Re: comparing data between two databases [message #306583 is a reply to message #306366] Fri, 14 March 2008 15:06 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I want to compare the tables between two schemas residing in two different database,
This question is/was a clear as mud.
Do you want to compare table structure(s) or compare table data?
The solution for each a radically different from each other.

Are you incapable or unwilling to use GOOGLE? Which is it?
Previous Topic: dbms_utility.table_to_comma output
Next Topic: Is It Possible To Read an data from tabel in which Column Defined as Type?
Goto Forum:
  


Current Time: Mon Dec 05 21:30:40 CST 2016

Total time taken to generate the page: 0.12267 seconds